sqlsever 字符串分割函数

sql字符串分割函数

源地址：https://www.jb51.net/article/28921.htm

https://www.cnblogs.com/aierong/archive/2008/11/19/sqlserver_split.html

 1 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SplitString]') and xtype in (N'FN', N'IF', N'TF'))
 2 drop function [dbo].[SplitString]
 3 GO
 4 SET ANSI_NULLS ON 
 5 GO 
 6 SET QUOTED_IDENTIFIER ON 
 7 GO 
 8 CREATE function [dbo].[SplitString] 
 9 ( 
10 @Input nvarchar(max), --input string to be separated 
11 @Separator nvarchar(max)=',', --a string that delimit the substrings in the input string 
12 @RemoveEmptyEntries bit=1 --the return value does not include array elements that contain an empty string 
13 ) 
14 returns @TABLE table 
15 ( 
16 [Id] int identity(1,1), 
17 [Value] nvarchar(max) 
18 ) 
19 as 
20 begin 
21 declare @Index int, @Entry nvarchar(max) 
22 set @Index = charindex(@Separator,@Input) 
23 while (@Index>0) 
24 begin 
25 set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1))) 
26 if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'') 
27 begin 
28 insert into @TABLE([Value]) Values(@Entry) 
29 end 
30 set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input)) 
31 set @Index = charindex(@Separator, @Input) 
32 end 
33 set @Entry=ltrim(rtrim(@Input)) 
34 if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'') 
35 begin 
36 insert into @TABLE([Value]) Values(@Entry) 
37 end 
38 return 
39 end
40 
41 GO

 

转载于:https://www.cnblogs.com/towntowner/p/9716926.html