SQL - SUBSTRING and CHARINDEX

Question

Possible Duplicate:
T-SQL: Opposite to string concatenation - how to split string into multiple records
Splitting variable length delimited string across multiple rows (SQL)

I have a database table that contains column data like this:

Data (field name)
1111,44,666,77
22,55,76,54
32,31,56

I realise this is a very poor design because it is not normalised (I didn't design it - I inherited it). Is there a query that will return the data like this:

1111
44
666
77
22
55
76
54
32
31
56

I am use to using CHARINDEX and SUBSTRING, but I cannot think of a way of doing this as the number of elements in each cell (delimited by a comma) is unknown.

Answer 1

I created a table called [dbo].[stack] and filled it with the data you provided and this script produced what you needed. There may be a more efficient way of doing this but this works exactly how you requested.

    BEGIN
        DECLARE @tmp TABLE (data VARCHAR(20))
        DECLARE @tmp2 TABLE (data VARCHAR(20))

        --Insert all fields from your table
        INSERT INTO @tmp (data)
        SELECT [data]           
        FROM   [dbo].[stack] -- your table name here

        --Loop through all the records in temp table
        WHILE EXISTS (SELECT 1
                                    FROM   @tmp)            
            BEGIN 
                DECLARE @data VARCHAR(100) --Variable to chop up 
                DECLARE @data1 VARCHAR(100) -- Untouched variable to delete from tmp table
                SET @data =  (SELECT TOP 1 [data]
                                            FROM   @tmp)
                SET @data1 =  (SELECT TOP 1 [data]
                                             FROM   @tmp)

                --Loop through variable to get individual value 
                WHILE PATINDEX('%,%',@data) > 0     
                    BEGIN
                        INSERT INTO @tmp2
                        SELECT SUBSTRING(@data,1,PATINDEX('%,%',@data)-1);
                        SET @data = SUBSTRING(@data,PATINDEX('%,%',@data)+1,LEN(@data))
                        IF PATINDEX('%,%',@data) = 0
                            INSERT INTO @tmp2
                            SELECT @data            
                    END

                DELETE FROM @tmp 
                WHERE [data] = @data1

            END     

        SELECT * FROM @tmp2     
    END

Answer 2

You can use CTE to split the data:

;with cte (DataItem, Data) as
(
  select cast(left(Data, charindex(',',Data+',')-1) as varchar(50)) DataItem,
         stuff(Data, 1, charindex(',',Data+','), '') Data
  from yourtable
  union all
  select cast(left(Data, charindex(',',Data+',')-1) as varchar(50)) DataItem,
    stuff(Data, 1, charindex(',',Data+','), '') Data
  from cte
  where Data > ''
) 
select DataItem
from cte

See SQL Fiddle with Demo

Result:

| DATAITEM |
------------
|     1111 |
|       22 |
|       32 |
|       31 |
|       56 |
|       55 |
|       76 |
|       54 |
|       44 |
|      666 |
|       77 |

Or you can create a split function:

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
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;

Which you can use when you query and this will produce the same result:

select s.items declaration
from yourtable t1
outer apply dbo.split(t1.data, ',') s

Answer 3

SELECT REPLACE(field_name, ',', ' ') from table

EDIT: Never mind this answer as you changed your question.

Answer 4

Not talking about performance, you can concatenate the data in a single column and then split it.

Concatenate data: http://sqlfiddle.com/#!6/487a4/3

Split it: T-SQL: Opposite to string concatenation - how to split string into multiple records

Answer 5

Take a look at this article referenced in a similar question:

http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str

If you create the function that they have in that article, you can call it using:

select * from dbo.fn_ParseText2Table('100|120|130.56|Yes|Cobalt Blue','|')