[MSSQL]实现SQL Server中的切割字符串SplitString标量函数
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*by kudychen 2011-9-28 */CREATE function .(@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 ( int identity(1,1), 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() 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() 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 from .(@str1, ',', 1)select from .(@str2, '###', 1)select from .(@str3, '###', 0)
页:
[1]