SQL Function, splitting text into individual rows (output) 2

[M8KWR]

Hi,

I have a cell with the following 1; 2; 3; 4; 5

I am looking at a way to output this along with an ID reference column, so it look something like this

ID FIELD
1 1
1 2
1 3
1 4
1 5

I hope i have explained myself correctly.

Many thanks for any help in advance.

 

[RiverGuy]

Google "SQL Server split function." There's lots of examples out there.

 

[M8KWR]

I have found this function

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       
      
    select @idx = 1       
        if len(@String)<1 or @String is null  return       
      
    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

Then you use the following

select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')

But I am struggling to put an actual field from a table, can any body help, or am i doing something really silly!!!

 

[RiverGuy]

It would be something like:

DECLARE @Values VARCHAR(8000)
SELECT @Values = ColumnName FROM SomeTable WHERE SomeColumn = SomeCriteria

select top 10 * from dbo.split(@Values,',')

Now, the example above will work fine if you can narrow your string down to one value -- if you want to run this over an entire resultset with multiple delimited values, you would have to use more logic to concatenate them all into one string.

 

[M8KWR]

Riverguy, thanks for you help.

But one last question.

I need to pass an ID number through the first statement and then the second, so i have this displayed along with what is splits in the function

Could you offer me any assistance please.

 

[gmmastros]

But I am struggling to put an actual field from a table, can any body help, or am i doing something really silly!!!

It kinda depends on what you are trying to do. If you have a column in a table that has delimited data, and you want to use the split function on each row, you can use the Cross Apply functionality (available with SQL2005 and up).

Ex:

Declare @Temp Table(Id Int, Data VarChar(200))

Insert Into @Temp Values(1, 'Red;Green;blue')
Insert Into @Temp Values(2, 'Apple;Banana;Grape')
Insert Into @Temp Values(3, 'Soda;Coffee;Water')

Select T.Id, X.Items
From   @Temp T
       Cross Apply dbo.Split(T.Data, ';') X

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

You need to use CROSS APPLY here.

Please see this MSDN thread for a reference (it also lists another function and gives a link to comprehensive study of the 'split' question)

http://social.msdn.microsoft.com/Fo...eb5ba52/#d7811e25-4670-4dae-afd5-d9ed61da2e93

PluralSight Learning Library

 

[M8KWR]

many thanks for all your help, finally sorted it.