tsql - extract only a portion of a dynamic string

Question

I have a table with a column that holds a string such as:

xx-xx-xxx-84
xx-25-xxx-xx
xx-xx-123-xx

I want to go ahead and query out the numbers only, but as you can see, the numbers are placed at different places in the string every time. Is there away to query for only the numbers in the string?

Thank you, I appreciate your time!

http://stackoverflow.com/questions/11333078/sql-take-just-the-numeric-values-from-a-varchar — Seano666, Apr 20 '16 at 22:33

score 0 · Answer 1 · answered Apr 20 '16 at 22:42

This requires repeated application of string functions. One method that helps with all the nesting is using OUTER APPLY. Something like this:

select t3.col
from t outer apply
     (select t.*, patindex(t.col, '[0-9]') - 1 as numpos) t1 outer apply
     (select t1.*, substring(t1.col, t1.numpos, len(t1.col)) as col2) t2 outer apply
     (select t2.*,
             (case when col2 like '%-%'
                   then substring(t2.col, charindex('-', t2.col))
                   else t2.col
              end) as col3
    ) t3

score 0 · Answer 2 · answered Apr 21 '16 at 09:00

The easy way (sure if only 'x' and '-' are in strings):

SELECT REPLACE(REPLACE(s,'x',''),'-','') FROM T

Or if X can be any non digital character then using PATINDEX() function:

SELECT S, SUBSTRING(S,
                    PATINDEX('%[0-9]%',s),
                    PATINDEX('%[0-9][^0-9]%',s)
                    +PATINDEX('%[0-9]',s)
                    +1
                    -PATINDEX('%[0-9]%',s)) as digit
FROM T

tsql - extract only a portion of a dynamic string

2 Answers2