string to Table

[abenitez77]

I have a column that has comments like the string below. I wan to get all the 7 digit numbers and put it into 1 table.

"2:42 1/29 On R.P.C resolved vendor was instructed on Friday failed to send 2020817 813 811 810 806 796 790 785 763 po 5480879 can4769753s dc 890 885 875 865 860 855 840 830 810 shipment not sent RESOLVED"


results:
2020817
5480879
4769753

 

[markros]

I solved it using recursive cte, although I think Numbers table solution is also available:

declare @t table (id int identity (1,1) primary key, String varchar(max))
insert into @t values ('"2:42 1/29 On R.P.C  resolved  vendor was instructed on Friday  failed to send  2020817 813 811 810 806 796 790  785 763 po  5480879 can4769753s dc  890 885 875 865 860 855 840 830 810 shipment not sent RESOLVED"')

;with cte as (select ID, String, SUBSTRING(String, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', String),7) as sNumber,
SUBSTRING(String, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', String)+8, LEN(String)) as Rest
from @t  where String like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
UNION ALL
select ID, String, SUBSTRING(Rest, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', Rest),7) as sNumber,
SUBSTRING(Rest, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', Rest)+8, LEN(Rest)) as Rest
from cte  where Rest like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%')

select * from cte

PluralSight Learning Library

 

[drlex]

gmmastros has a nice blog post to start you off here:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server

(assuming he's too modest to mention it).

soi là, soi carré