[MSSQL]实现SQL Server中的切割字符串SplitString标量函数

ycvodzf

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*by kudychen 2011-9-28 */CREATE function [dbo].[SplitString](@Input nvarchar(max), --input string to be separated@Separator nvarchar(max)=',', --a string that delimit the substrings in the input string@RemoveEmptyEntries bit=1 --the return value does not include array elements that contain an empty string)returns @TABLE table ([Id] int identity(1,1),[Value] nvarchar(max)) asbegin declare @Index int, @Entry nvarchar(max)set @Index = charindex(@Separator,@Input)while (@Index>0)beginset @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')begininsert into @TABLE([Value]) Values(@Entry)endset @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))set @Index = charindex(@Separator, @Input)endset @Entry=ltrim(rtrim(@Input))if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')begininsert into @TABLE([Value]) Values(@Entry)endreturnend 如何使用：declare @str1 varchar(max), @str2 varchar(max), @str3 varchar(max)set @str1 = '1,2,3'set @str2 = '1###2###3'set @str3 = '1###2###3###'select [Value] from [dbo].[SplitString](@str1, ',', 1)select [Value] from [dbo].[SplitString](@str2, '###', 1)select [Value] from [dbo].[SplitString](@str3, '###', 0)