Splitting of comma separated values in a column

Question

0.00/5 (No votes)

See more:

I have been asked to create a function that will do the following:

I have three columns:

Parent Number...............SNDDI............................................DDI
12457847549........24578487557,5559864127
54876458722..........................................................54685913487,46589857855

I need to seperate the SNDDI & the DDI numbers and put in a row with the parent number as below and type of number(which column the number came from)

Parent Number.....SNDDI.................DDI..............TYPE
---------------------------------------------------------------
12457847549.......24578487557............................SNDDI
12457847549.......5559864127.............................SNDDI
12457847549.......22645134996............................SNDDI
54876458722.............................54685913487......DDI
54876458722.............................46589857855......DDI

Posted 24-Jun-13 23:43pm

Love Toys

Updated 25-Jun-13 0:43am

Mike Meinz

v3

Add a Solution

Comments

Maciej Los 25-Jun-13 5:49am

Does your data are separated by many commas and dots?

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-06-25T00:07:00

I really do recommend you to use Common Table Expressions[^].

Try this:

SQL

DECLARE @tmp TABLE ([Parent Number] BIGINT, SNDDI VARCHAR(255), DDI VARCHAR(255))

INSERT INTO @tmp ([Parent Number], SNDDI, DDI)
SELECT 12457847549 AS [Parent Number], '24578487557,5559864127' AS SNDDI, '' AS DDI
UNION ALL SELECT 54876458722, '', '54685913487,46589857855'

--SELECT *
--FROM @tmp

;WITH sndis AS
(
	SELECT [Parent Number], LEFT(SNDDI, CHARINDEX(',', SNDDI)-1) AS ChildNumber, 'SNDDI' AS TypeOfNumber, RIGHT(SNDDI, LEN(SNDDI)- CHARINDEX(',', SNDDI)) AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',', SNDDI)>0
	UNION ALL
	SELECT [Parent Number], LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS ChildNumber, 'SNDDI' AS TypeOfNumber, RIGHT(Remainder, LEN(Remainder)- CHARINDEX(',', Remainder)) AS Remainder
	FROM sndis
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT [Parent Number], Remainder AS ChildNumber, 'SNDDI' AS TypeOfNumber, NULL AS Remainder
	FROM sndis
	WHERE CHARINDEX(',', Remainder)=0
), ddis AS
(
	SELECT [Parent Number], LEFT(DDI, CHARINDEX(',', DDI)-1) AS ChildNumber, 'DDI' AS TypeOfNumber, RIGHT(DDI, LEN(DDI)- CHARINDEX(',', DDI)) AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',', DDI)>0
	UNION ALL
	SELECT [Parent Number], LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS ChildNumber, 'DDI' AS TypeOfNumber, RIGHT(Remainder, LEN(Remainder)- CHARINDEX(',', Remainder)) AS Remainder
	FROM ddis
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT [Parent Number], Remainder AS ChildNumber, 'DDI' AS TypeOfNumber, NULL AS Remainder
	FROM ddis
	WHERE CHARINDEX(',', Remainder)=0
)
SELECT [Parent Number], ChildNumber, TypeOfNumber
FROM (
	SELECT [Parent Number], ChildNumber, TypeOfNumber
	FROM sndis
	UNION ALL
	SELECT [Parent Number], ChildNumber, TypeOfNumber
	FROM ddis
	) AS T

Result:

Parent Number   ChildNumber     TypeOfNumber
12457847549	24578487557	SNDDI
12457847549	5559864127	SNDDI
54876458722	54685913487	DDI
54876458722	46589857855	DDI

More:
Using Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]

OriginalGriff · Answer 2 · 2013-06-24T23:56:00

Solution 1

This isn't exactly what you need - your requirement is pretty specific - but it should give you an idea how to start handling it: Using comma separated value parameter strings in SQL IN clauses[^]

Posted 24-Jun-13 23:56pm

OriginalGriff

damodara naidu betha · Answer 3 · 2013-06-26T00:14:00

Hi try this code block

SQL

DECLARE @tmp TABLE ([Parent Number] BIGINT, SNDDI VARCHAR(255), DDI VARCHAR(255))

 INSERT INTO @tmp ([Parent Number], SNDDI, DDI)
 SELECT 12457847549 AS [Parent Number],'24578487557,5559864127' AS SNDDI,'' AS DDI
 UNION ALL
 SELECT 54876458722, '', '54685913487,46589857855'


;WITH SplitSNDDISCTE AS (
    SELECT [Parent Number],CAST('<i>' + REPLACE(SNDDI, ',', '</i><i>') + '</i>' AS XML) AS
    SNDDIS,DDI FROM @tmp
    ),
    SplitDDICTE AS (
        SELECT [Parent Number],x.i.value('.', 'VARCHAR(MAX)') AS SNDDI,
    CASE WHEN LTRIM(RTRIM(x.i.value('.', 'VARCHAR(MAX)'))) <> ''
        THEN 'SNDDI' ELSE 'DDI' END As Type,
        CAST('<i>'+ REPLACE(DDI,',','</i><i>') +'</i>' AS XML) AS DDIS
        FROM SplitSNDDISCTE CROSS APPLY SNDDIS.nodes('//i') x(i)
    )

   SELECT [Parent Number],SNDDI,a.i.value('.','VARCHAR(MAX)') AS DDI,Type
   FROM SplitDDICTE CROSS APPLY DDIS.nodes('//i') a(i)

Love Toys · Answer 4 · 2013-06-25T00:00:00

Solution 2

No numbers are separated by commas not dots I had to add the dots so it looked right on the page
Both the snddi and the ddi both have numbers in separated by comma some lines have no numbers some lines have both and some li es have 5 in one and 2 in another

Posted 25-Jun-13 0:00am

Love Toys

Comments

Maciej Los 25-Jun-13 6:09am

This is not an answer. Please, remove it. If you want to post a comment, use "Have a Question or Comment" widget, instead posting it as a "solution".

Splitting of comma separated values in a column

4 solutions

Solution 3

Solution 1

Solution 4

Solution 2

Add your solution here

Preview 0