string to table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

string to table

Author

Topic

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2012-01-19 : 10:45:01

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-01-19 : 11:12:58

Example:

DECLARE @comment VARCHAR(1000)
SET @comment='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 n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<LEN(@comment))
	SELECT SUBSTRING(@comment,n,7) FROM n
	WHERE PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',SUBSTRING(@comment,n,7))>0
	OPTION (MAXRECURSION 1000)

Simply change the @comment to match your column name and CROSS JOIN the query with your table. The technique is similar to that described here: http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources