Link to home
Start Free TrialLog in
Avatar of Richard
RichardFlag for United States of America

asked on

Split comma delimited field into multiple records

I have a table that has one field that has values that are comma delimited. I would like use a spit function create a seperate records in the same table for each comma delimited value in this field.

f1    f2           f3             f4

a      b           c            1,2,3
d      e           f            54,44,33

would become

a      b           c            1
a      b           c            2
a      b           c            3
d      e           f            54
d      e           f            44
d      e           f            33



I have tried to use the split function below in a select but I get an error invalid object dbo.fsplit

SELECT     dbo.fSplit(f4, ',') AS Expr1
FROM         dbo.table



ALTER FUNCTION dbo.fn_Split
(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
      @value varchar(8000),
      @bcontinue bit,
      @iStrike smallint,
      @iDelimlength tinyint

IF @sDelim = 'Space'
      BEGIN
      SET @sDelim = ' '
      END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
      BEGIN
      WHILE @bcontinue = 1
            BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
 
            IF CHARINDEX(@sDelim, @sText)>0
                  BEGIN
                  SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
                        BEGIN
                        INSERT @retArray (idx, value)
                        VALUES (@idx, @value)
                        END
                  
--Trim the element and its delimiter from the front of the string.
                  --Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
                  SET @idx = @idx + 1
                  SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
            
                  END
            ELSE
                  BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
                        BEGIN
                        INSERT @retArray (idx, value)
                        VALUES (@idx, @value)
                        END
                  --Exit the WHILE loop.
SET @bcontinue = 0
                  END
            END
      END
ELSE
      BEGIN
      WHILE @bcontinue=1
            BEGIN
            --If the delimiter is an empty string, check for remaining text
            --instead of a delimiter. Insert the first character into the
            --retArray table. Trim the character from the front of the string.
--Increment the index and loop.
            IF DATALENGTH(@sText)>1
                  BEGIN
                  SET @value = SUBSTRING(@sText,1,1)
                        BEGIN
                        INSERT @retArray (idx, value)
                        VALUES (@idx, @value)
                        END
                  SET @idx = @idx+1
                  SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
                  
                  END
            ELSE
                  BEGIN
                  --One character remains.
                  --Insert the character, and exit the WHILE loop.
                  INSERT @retArray (idx, value)
                  VALUES (@idx, @sText)
                  SET @bcontinue = 0      
                  END
      END

END

RETURN
END


Thanks


       
Avatar of sasapopovic
sasapopovic
Flag of Serbia image

Try this:

SELECT     dbo.fnSplit(f4, ',') AS Expr1
FROM         dbo.table
Avatar of RickBeebe
RickBeebe

Even better:

SELECT     dbo.fn_Split(f4, ',') AS Expr1
FROM         dbo.table
Avatar of Richard

ASKER

Nope. In my question I had a typo in the select but in my query it was correct.

SELECT     dbo.fn_Split(f4, ',') AS Expr1
FROM         dbo.table

Won't work. The function works if I give it a string value say of '111,2222,3333," but not when I do a select with the field name as the input string it fails.
This is a table-valued function:

SELECT *
FROM DBO.FN_SPLIT('111,2222,3333',',')

Returns an array of values
idx, value
0     111
1     2222
2     3333

You will need a loop to process your rows from f4.
Avatar of Richard

ASKER

Ok. Then what is the best way to take at table like

f1    f2           f3             f4

a      b           c            1,2,3
d      e           f            54,44,33

and end up with ?

a      b           c            1
a      b           c            2
a      b           c            3
d      e           f            54
d      e           f            44
d      e           f            33

CREATE PROCEDURE DBO.USP_SPLIT_COLUMN AS
DECLARE CUR_TABLE CURSOR FOR
SELECT f1,f2,f3,f4
FROM DBO.TMP_TABLE
DECLARE @f1 varchar(50),
@f2 varchar(50),
@f3 varchar(50),
@f4 varchar(8000)

BEGIN
      CREATE TABLE #tmp (f1 varchar(50),f2 varchar(50),f3 varchar(50),f4 varchar(7800))
      OPEN CUR_TABLE
      FETCH NEXT FROM CUR_TABLE
      INTO @f1,@f2,@f3,@f4
      WHILE @@FETCH_STATUS = 0
      BEGIN
            INSERT INTO #tmp(f1,f2,f3,f4)
          SELECT @f1,@f2,@f3,value f4
          FROM DBO.FN_SPLIT(@f4,',')
          FETCH NEXT FROM CUR_TABLE
          INTO @f1,@f2,@f3,@f4
      END
      CLOSE CUR_TABLE
      DEALLOCATE CUR_TABLE
      SELECT * FROM #tmp
END
This is a classic example of denormalised data - column f4 is not even in first normal form! And looking at the next table I see a lot of redundancy there as well e.g. a,b,c repeated three times.

Without knowing what your columns are I suggest that you should be looking at something like

x1 -> ( a,b,c), x2 -> (d,e,f)

and 1 -> x1, 2-> x1, 3-> x1, 54 -> x2, 44 -> x2, 33 -> x2

However this may all be due to your example...

You want to be able to say - for each comma separated value in f4, insert ??? into table2 - so do it in code.

Retrieve your first recordset and then get the f4 column - split it into an array of integers and then for each value, generate an insert for the rest.

In a way, data like this is non-sql and difficult (but not impossible) to handle in a query.

ASKER CERTIFIED SOLUTION
Avatar of RickBeebe
RickBeebe

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial