Solved

Split comma delimited field into multiple records

Posted on 2006-06-12
8
3,527 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Many to Many From without subform 11 78
When to use a Unique Index? A Clustered Index? 5 73
SQL: splitting Columns 6 34
Any benefit to adding a Clustered index here? 4 33
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
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…

738 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