SET ANSI_NULLS ON 

GO  SET QUOTED_IDENTIFIER ON  GO  / 

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)  )  as  begin  declare @Index int, @Entry nvarchar(max)  set @Index = charindex(@Separator,@Input)  while (@Index>0)  begin  set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))  if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')  begin  insert into @TABLE([Value]) Values(@Entry)  end  set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))  set @Index = charindex(@Separator, @Input)  end  set @Entry=ltrim(rtrim(@Input))  if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')  begin  insert into @TABLE([Value]) Values(@Entry)  end  return  end  使用方法：

declare @str1 varchar(max)

set @str1 = '111,25,38'

select [Value] from SplitString(@str1, ',', 1)

本文转自51GT51CTO博客，原文链接： http://blog.51cto.com/yataigp/2049792，如需转载请自行联系原作者