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
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
1
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
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