SPLIT FUNCTION

----------------------SPLIT names  using STRING FUNCTIONS--------------------------


/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 08/06/2012 16:16:27 ******/

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

-- select distinct mls_mail_to from gpc_mails where MLS_MESSAGE_FROM='NC' and mls_mail_status is null

create function [dbo].[Split] (@String varchar(8000), @Delimiter char(1))

Returns

@Results Table (Items varchar(8000))

As

Begin

Declare @Index int

Declare @Slice varchar(8000)

Select @Index = 1

If @String Is NULL Return

While @Index != 0

Begin

Select @Index = CharIndex(@Delimiter, @String)

If (@Index != 0)

Select @Slice = left(@String, @Index - 1)

else

Select @Slice = @String

Insert into @Results(Items) Values (@Slice)

Select @String = right(@String, Len(@String) - @Index)

If Len(@String) = 0 break

End

Return

End

 --select * from dbo.Split('1,vagalla,SURESH,reddy',',,')