SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/****************分割字符串 ****************/CREATE function [dbo].[SplitString](@Input nvarchar(max), @Separator nvarchar(max)=',', @RemoveEmptyEntries bit=1 )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

调用创建的sql函数

declare @s varchar(100),@sql varchar(1000),@split VARCHAR(10),@index INTSET @split='|';SET @index=1;SET @s='|21|2106|';select COUNT(*) from [dbo].[SplitString](@s, @split, 1)select * from [dbo].[SplitString](@s, @split, 1)

 结果