Click here to Skip to main content
14,691,836 members
Please Sign up or sign in to vote.
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
Updated 25-Jun-13 1:43am
v3
Comments
Maciej Los 25-Jun-13 5:49am
   
Does your data are separated by many commas and dots?

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

Try this:
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[^]
   
v2
Comments
Love Toys 25-Jun-13 7:25am
   
that works really well with hardcoded numbers but the numbers are held within a table

Table = Numbers
columns = ParentNumber varchar(max), snddi varchar(max), ddi varchar(max)
also seems to fall over when you have a number in both SNDDI and DDI columns

thank you for your help with this by the way :)
Maciej Los 25-Jun-13 7:40am
   
There is not "hardcoded numbers". In the first line i create temporary table (as a variable) into which i put the numbers...

Change the name of table @tmp with Numbers and see what will happen. ;)

Mark this answer as solution, if it was helpful (use green button).
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[^]
   
Hi try this code block

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)
   
v4
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
   
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".

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900