Split Function in Separated by delimeter in a Strings of a column

Some time we need to extract the values from a string that time we use to do a lots of RND that time we can use given below function. This function is similar to the VB split function. It takes a nvarchar delimeted list and delimeter and returns a table with the values split on delimeter.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))

returns @temptable TABLE (Data varchar(8000))

as

begin

declare @idx int

declare @slice varchar(8000)

select @idx = 1

if len(@String)<1>

while @idx!= 0

begin

set @idx = charindex(@Delimiter,@String)

if @idx!=0

set @slice = left(@String,@idx - 1)

else

set @slice = @String

if(len(@slice)>0)

insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)

if len(@String) = 0 break

end

return

end

How to use this function?

Ok Let me explain

we have column jobids in a table TABLE1 which is a string. We have to extract the data separated with comma.

select * from Table1

Result

300-35,300-34

Now we have to separate the delimited by comma.

Declare @jobids as varchar(50)

select @jobids=jobids from Table1

select * from dbo.split(@jobids,',')

Result-

Data

300-35

300-34