SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--DEClARE @str varchar(500)='a,b2,v5,d3,ew,2,3,dd'

--select  [dbo].[split](@str,',',8)

CREATE FUNCTION [dbo].[split]

(@str nvarchar(4000),@code varchar(10),@no int )

RETURNS varchar(200)

AS

BEGIN

declare @intLen int

declare @count int

declare @indexb  int

declare @indexe  int

set @intLen=len(@code)

set @count=0

set @indexb=1

if @no=0

  if charindex(@code,@str,@indexb)<>0

     return left(@str,charindex(@code,@str,@indexb)-1)

  else

     return @str

while charindex(@code,@str,@indexb)<>0

  begin

       set @count=@count+1

       if @count=@no

         break

       set @indexb=@intLen+charindex(@code,@str,@indexb)

  end

if @count=@no

  begin

      set @indexe=@intLen+charindex(@code,@str,@indexb)

          if charindex(@code,@str,@indexe)<>0

             return substring(@str,charindex(@code,@str,@indexb)+len(@code),charindex(@code,@str,@indexe)-charindex(@code,@str,@indexb)-len(@code))

          else

             return right(@str,len(@str)-charindex(@code,@str,@indexb)-len(@code)+1)

  end

return NULL

END

--select dbo.split("列名",',',0)