how to extract a particular id from the string using sql

Question

I want to extract a particular ids from the records in a table.For example i have a below table

Id   stringvalue
1    test (ID 123) where another ID 2596
2    next ID145 and the condition I(ID 635,897,900)

I want the result set as below

ID SV
1  123,2596
2  145,635,897,900

i have tried the below query which extracts only one ID from the string:

Select Left(substring(string,PATINDEX('%[0-9]%',string),Len(string)),3) from Table1

This is gonna be a pain in sql server. Better do in some programming language using regex. — Giorgi Nakeuri, Nov 12 '15 at 05:21

RNA Team · Accepted Answer · 2015-11-12T11:50:05.863

I seriously don't encourage the T-SQL approach (as SQL is not meant to do this), however, a working version is presented below -

Try this

DECLARE @T TABLE(ID INT IDENTITY,StringValue VARCHAR(500))
INSERT INTO @T 
    SELECT 'test (ID 123) where another ID 2596' UNION ALL
    SELECT 'next ID145 and the condition I(ID 635,897,900)'

;WITH SplitCTE AS(
SELECT 
    F1.ID, 
    X.SplitData 
    ,Position = PATINDEX('%[0-9]%', X.SplitData)
FROM (
    SELECT *,   
    CAST('<X>'+REPLACE(REPLACE(StringValue,' ',','),',','</X><X>')+'</X>' 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
    WHERE PATINDEX('%[0-9]%', X.SplitData) > 0),
    numericCTE AS(
    SELECT 
        ID
        ,AllNumeric = LEFT(SUBSTRING(SplitData, Position, LEN(SplitData)), PATINDEX('%[^0-9]%', SUBSTRING(SplitData, Position, LEN(SplitData)) + 't') - 1)      
    FROM SplitCTE  
)

SELECT
    ID
    ,STUFF(( SELECT ',' + c1.AllNumeric
                FROM numericCTE c1
                WHERE c1.ID = c2.ID
                FOR XML PATH(''),TYPE)
                .value('.','NVARCHAR(MAX)'),1,1,'') AS SV
FROM numericCTE c2
GROUP BY ID

/* Result

ID  SV
1   123,2596
2   145,635,897,900

*/

However, I completely agree with @Giorgi Nakeuri. It is better to use some programming language (if you have that at your disposal) and use regular expression for the same. You can figure out that, I have used REPLACE function two times, first to replace the blank space and second to replace the commas(,). Hope you will get some idea to move on.

Thanks for the effort, but you are just encourage really bad practises. +1 if you remove the SQL, and leave the comment at the end :-) — TFD, Nov 12 '15 at 08:51

how to extract a particular id from the string using sql

1 Answers1