Solved

Split comma delimited field into multiple records

Posted on 2006-06-12
8
3,517 Views
Last Modified: 2008-07-26
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


       
0
Comment
Question by:guilloryt
8 Comments
 
LVL 9

Expert Comment

by:sasapopovic
ID: 16887213
Try this:

SELECT     dbo.fnSplit(f4, ',') AS Expr1
FROM         dbo.table
0
 
LVL 3

Expert Comment

by:RickBeebe
ID: 16887644
Even better:

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

Author Comment

by:guilloryt
ID: 16887690
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.
0
 
LVL 3

Expert Comment

by:RickBeebe
ID: 16888009
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.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:guilloryt
ID: 16888028
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

0
 
LVL 3

Expert Comment

by:RickBeebe
ID: 16888311
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
0
 
LVL 9

Expert Comment

by:gabeso
ID: 16888318
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.

0
 
LVL 3

Accepted Solution

by:
RickBeebe earned 500 total points
ID: 16888447
Additional details:  (if needed)

First,  Create the procedure:

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
     RETURN
END


Then, execute the procedure:

DECLARE @V_RC INT
EXEC @V_RC = DBO.USP_SPLIT_COLUMN
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now