现在sqlserver想实现一个功能:有个apply_industry字段想根据逗号分割成多行便于后面的统计
具体实现方式如下:
1、增加Split函数
1 -- 字符串按字符分成多条数据(@String:待分隔字符串, @Delimiter:分隔符) 2 -- demo: select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',') 3 -- 结果如下: 4 -- items(列名) 5 -- Chennai 6 -- Bangalore 7 -- Mumbai 8 9 CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1)) 10 returns @temptable TABLE (items varchar(8000)) 11 as 12 begin 13 declare @idx int 14 declare @slice varchar(8000) 15 select @idx = 1 16 if len(@String)<1 or @String is null return 17 while @idx!= 0 18 begin 19 set @idx =charindex(@Delimiter,@String) 20 if @idx!=0 21 set @slice =left(@String,@idx - 1) 22 else 23 set @slice = @String 24 if(len(@slice)>0) 25 insert into @temptable(items)values(@slice) 26 set @String =right(@String,len(@String)- @idx) 27 if len(@String)= 0 break 28 end 29 return 30 end
2、执行语句
SELECT id, b.apply_industry FROM db_basic b CROSS APPLY dbo.Split(b.apply_industry,',') AS a
结果如下