Suppose we have a requirement that we have to extract numbers from a set of alphanumeric strings.E.g. the input will be
ID StringValue
1 My Address is #456, 13th Main, 16th Cross, Area code - 560009
2 My first mobile number:91161181100. Second mobile number:- 1111111111.
Third one is
: 1212121212
The respective output will be
ID CommaSeperatedValues
1 456,13,16,560009
2 91161181100,1111111111,1212121212
Inorder to solve this problem,first we will split the strings with space(' ') as the delimeter as shown under
DECLARE @T TABLE(ID INT IDENTITY,StringValue VARCHAR(500))
INSERT INTO @T
SELECT 'My Address is #456, 13th Main, 16th Cross, Area code - 560009' UNION ALL
SELECT 'My first mobile number:91161181100. Second mobile number:- 1111111111. Third one is: 1212121212'
;WITH SplitCTE AS(
SELECT
F1.ID,
X.SplitData
,Position = PATINDEX('%[0-9]%', X.SplitData)
FROM (
SELECT *,
CAST(''+REPLACE(StringValue,' ','')+'' AS XML) AS XmlFilter
FROM @T F
)F1
CROSS APPLY
(
SELECT fdata.D.value('.','varchar(50)') AS SplitData
FROM f1.xmlfilter.nodes('X') AS fdata(D)) X
)
SELECT *
FROM SplitCTE
The output is as expected
ID SplitData Position
1 My 0
1 Address 0
1 is 0
1 #456 2
1 0
1 13th 1
1 Main 0
1 0
1 16th 1
1 Cross 0
1 0
1 Area 0
1 code 0
1 - 0
1 560009 1
2 My 0
2 first 0
2 mobile 0
2 number:91161181100. 8
2 Second 0
2 mobile 0
2 number:- 0
2 1111111111. 1
2 Third 0
2 one 0
2 is: 0
2 1212121212 1
Now, it is quite silmple to figure out the set of numeric and alphanumeric strings by using the
PATINDEX function as shown under
WHERE PATINDEX('%[0-9]%', SplitData) > 0
The output will be
ID SplitData Position
1 #456 2
1 13th 1
1 16th 1
1 560009 1
2 number:91161181100. 8
2 1111111111. 1
2 1212121212 1
The next task is to find out the numeric values which can be done by using the following SQL Code snippet
SELECT ID ,AllNumeric = LEFT(SUBSTRING(SplitData, Position, LEN(SplitData)), PATINDEX('%[^0-9]%', SUBSTRING(SplitData, Position, LEN(SplitData)) + 't') - 1)
FROM SplitCTE
The output will be
ID AllNumeric
1 456
1 13
1 16
1 560009
2 91161181100
2 1111111111
2 1212121212
So now as a final step, we need to use the FORXMLPATH and group by the items by using the ID
SELECT ID,STUFF(( SELECT ',' + c1.AllNumeric
FROM numericCTE c1
WHERE c1.ID = c2.ID
FOR XML PATH(''),TYPE)
.value('.','NVARCHAR(MAX)'),1,1,'') AS CommaSeperatedValues
FROM numericCTE c2
GROUP BY ID
The result
ID CommaSeperatedValues
1 456,13,16,560009
2 91161181100,1111111111,1212121212