I have say a table with as under
Values
--------
12Null345XXX23456
6712356
Expected Output
----------------
23456
123
How can I do this using TSql(prefereable set based) ?
Thanks
Add a comment
|
1 Answer
So, you want the longest substring of consecutive numeric digits? Maybe something like this (t here is recreating your original table; if it already exists, skip that CTE and use your table)?:
;with ten as (
select i from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) x(i)
), subs as (
select SUBSTRING('0123456789',s.i,l.i) as sub, l.i as run
from ten as s
join ten as l on s.i+l.i<=11
), t as (
select s
from (
values ('12Null345XXX23456'),('6712356'))x(s)
)
select x.sub
from t
cross apply (select top 1 subs.sub
from subs
where CHARINDEX(subs.sub,t.s)>0
order by subs.run desc) x
