string to table
-
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
-
Using a numbers/tally table
DECLARE @t TABLE (Data VARCHAR(1000))INSERT @t (Data)
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')
SELECT SUBSTRING(Data,Number,7) AS Val
FROM @t
INNER JOIN master.dbo.spt_values ON Number BETWEEN 1 AND LEN(Data)-6 AND type='P'
AND SUBSTRING(Data,Number,7) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
AND SUBSTRING(Data,Number+7,1) NOT LIKE '[0-9]'
AND (Number=1 OR SUBSTRING(Data,Number-1,1) NOT LIKE '[0-9]')
ORDER BY Number;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I can't seem to quote messages in my replys for some reason, but holy cow Mark, that is way more elegant than the tally table solution I had come up with. Awesome! Mine had the problem of grabbing 8 digit numbers as to 7 digit numbers (positions 1-7, then 2-8), but yours seems to take care of that. Impressive.
-
roryp 96873 (1/19/2012)
I can't seem to quote messages in my replys for some reason, but holy cow Mark, that is way more elegant than the tally table solution I had come up with. Awesome! Mine had the problem of grabbing 8 digit numbers as to 7 digit numbers (positions 1-7, then 2-8), but yours seems to take care of that. Impressive.Ta for the feedback!
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Mark, this is awesome! thanks...i think i will be using tally tables more often.
Can yo please explain what AND type='P' is ?
and how does Number work? is that a column in spt_values?
-
Agreed. That is pretty slick Mark. I was going to use Moden's splitter...
select * from
dbo.DelimitedSplit8K('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', ' ')
where ISNUMERIC(item) = 1
and DATALENGTH(Item) = 7
This is a lot faster but it missed 'can4769753s' because it didn't have a space.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I guess my only complaint is that you would have a problem if the text string was longer than 2048 characters. I suppose that could be fixed with a little creative work on the join though.
-
roryp 96873 (1/19/2012)
I guess my only complaint is that you would have a problem if the text string was longer than 2048 characters. I suppose that could be fixed with a little creative work on the join though.You can just join to a permanent numbers/tally table that can be as large as you need it. Jeff Moden's is probably 10 million, mine is 10k.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
GrassHopper (1/19/2012)
Mark, this is awesome! thanks...i think i will be using tally tables more often.Can yo please explain what AND type='P' is ?
and how does Number work? is that a column in spt_values?
spt_values is a system table pre-populate with numbers 1 to 2048 for type of 'P'. You may find it useful to build your own instead, see this
http://www.sqlservercentral.com/articles/T-SQL/62867/
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
You can just join to a permanent numbers/tally table that can be as large as you need it. Jeff Moden's is probably 10 million, mine is 10k.
Ya, on my system I would have used my tally table that goes up to a million, I was just finding something to nitpick at. 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply