Richard
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,DATALEN GTH(@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,DATALEN GTH(@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
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)-
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,DATALEN
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,DATALEN
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
Even better:
SELECT dbo.fn_Split(f4, ',') AS Expr1
FROM dbo.table
SELECT dbo.fn_Split(f4, ',') AS Expr1
FROM dbo.table
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.
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,333 3',',')
Returns an array of values
idx, value
0 111
1 2222
2 3333
You will need a loop to process your rows from f4.
SELECT *
FROM DBO.FN_SPLIT('111,2222,333
Returns an array of values
idx, value
0 111
1 2222
2 3333
You will need a loop to process your rows from f4.
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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT dbo.fnSplit(f4, ',') AS Expr1
FROM dbo.table