Solved

Split comma delimited field into multiple records

Posted on 2006-06-12
8
3,514 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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…

760 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

23 Experts available now in Live!

Get 1:1 Help Now