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
