分割字符串，并输出表格

Target:

                将输入的字符串用指定的分隔符进行分割，并以表的形式呈现分割后的数据。

Input：

                要进行分割的字符串
                分隔符
                开始位置
                结束位置

Output：

                字符串分割后生成的表

Code:

 

 1 create function SplitStringBySeparator
 2     (
 3     @string        varchar(8000)    ='Are,you,kidding,me,?',
 4     @separator    char            =',',
 5     @startpos    int                =null,
 6     @endpos        int                =null
 7     )
 8     returns @result table(string varchar(8000) null)
 9     as
10     begin
11                 -- 如果输入为空字符串，则退出
12         if @string is null or LTRIM(rtrim(@string)) = ''
13             return
14 
15         -- intialize @start
16         if @startpos is null or @startpos > LEN(@string) or @startpos <1
17             set @startpos = 1
18 
19         -- intialize @end
20         if @endpos is null or @endpos > LEN(@string) or @endpos < 1
21             set @endpos = LEN(@string)
22         
23         if @startpos > @endpos
24             return
25         
26         declare @str varchar(8000)=substring(@string, @startpos, @endpos - @startpos + 1);
27         
28         declare @end int = charindex(@separator, @str, 1)
29         
30         -- 如果第一次就没有找到或者找到的位置大于结束位置，直接就返回
31         if @end < 1 or @end > @endpos
32         begin
33             insert @result select @str
34             return
35         end
36 
37         if @separator is null
38             set @separator = ',';
39 
40         -- 开始CTE
41         with x as(
42             select SUBSTRING(t.str, 1, @end - @startpos) as str,
43                 @end + 1 as start,
44                 CHARINDEX(@separator, t.str, @end + 1) as tail
45             from (select @str as str) t
46             union all
47             select SUBSTRING(@str, x.start, (
48                 case when x.tail - x.start < 0 then len(@str) - x.start + 1
49                 else x.tail - x.start end
50                 )) as str,                        --str
51                 case when x.tail = 0 then 0
52                 else x.tail + 1 end as start,    --start
53                 CHARINDEX(@separator, @str, x.tail + 1) as tail        --tail
54             from x
55             where x.start > 0
56         )
57 
58         -- 得到分割后的字符串
59         insert @result select str from x
60         return
61     end

 

 

 select *
2 from (select 'a,b,c' as str) t
3 cross apply dbo.SplitStringBySeparator(t.str,',',1,100)
4 
5 select *
6 from dbo.SplitStringBySeparator('a,b,c,d',',',1,100) t