14,668,708 members
Rate this:
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 0:43am
v3
Maciej Los 25-Jun-13 5:49am

Does your data are separated by many commas and dots?

Rate this:

## Solution 3

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
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).
Rate this:

## 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[^]
Rate this:

## Solution 4

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
Rate this:

## 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
Maciej Los 25-Jun-13 6:09am