[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3542
  • Last Modified:

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


       
0
guilloryt
Asked:
guilloryt
1 Solution
 
sasapopovicCommented:
Try this:

SELECT     dbo.fnSplit(f4, ',') AS Expr1
FROM         dbo.table
0
 
RickBeebeCommented:
Even better:

SELECT     dbo.fn_Split(f4, ',') AS Expr1
FROM         dbo.table
0
 
guillorytAuthor Commented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
RickBeebeCommented:
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
 
guillorytAuthor Commented:
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
 
RickBeebeCommented:
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
 
gabesoCommented:
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
 
RickBeebeCommented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now