query extraction help - 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

General SQL Server Forums

New to SQL Server Programming

query extraction help

Author

Topic

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-09 : 11:46:22

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-09 : 11:48:42

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-09 : 11:58:35

isnumeric has problems (see here: http://www.captechconsulting.com/blog/mark-hudson/isnumeric-inconsistencies)

Don't rely on it

What you can do is use charindex to find the fist blank (from the left) and extract that portion, then use like [0-9] to see if it's numeric. if not, use Reverse() to reverse the string and try again. Using reverse this is effectively from the right

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-09 : 12:06:46

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-09 : 12:29:08

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-09 : 12:43:05

First off, use this splitter function: http://www.sqlservercentral.com/articles/Tally+Table/72993/

e.g.


with cte(mytext) as (
	select * from (values
		('0535 Golds Gym Westport'),
		('0548 Nautilus Fitness of Sherman'),
		('0704 Xtreme Family Fitness'),
		('0732 Blue Springs Fitness')
	) v(t)
)

select mytext, split.*
from cte
cross apply [Loyalty].[DelimitedSplit8K](mytext, ' ') split

yields:


mytext	              ItemNumber	Item
0535 Golds Gym Westport	1	0535
0535 Golds Gym Westport	2	Golds
0535 Golds Gym Westport	3	Gym
0535 Golds Gym Westport	4	Westport
0548 Nautilus Fitness of Sherman	1	0548
0548 Nautilus Fitness of Sherman	2	Nautilus
0548 Nautilus Fitness of Sherman	3	Fitness
0548 Nautilus Fitness of Sherman	4	of
0548 Nautilus Fitness of Sherman	5	Sherman
0704 Xtreme Family Fitness	1	0704
0704 Xtreme Family Fitness	2	Xtreme
0704 Xtreme Family Fitness	3	Family
0704 Xtreme Family Fitness	4	Fitness
0732 Blue Springs Fitness	1	0732
0732 Blue Springs Fitness	2	Blue
0732 Blue Springs Fitness	3	Springs
0732 Blue Springs Fitness	4	Fitness

With this, you can work on it token by token. The next step could be:


...

select mytext, split.*
from cte
cross apply [Loyalty].[DelimitedSplit8K](mytext, ' ') split
where split.Item NOT LIKE '%[^0-9]%'

which gets to:


mytext	                             ItemNumber	Item
0535 Golds Gym Westport	                1	0535
0548 Nautilus Fitness of Sherman	1	0548
0704 Xtreme Family Fitness	        1	0704
0732 Blue Springs Fitness	        1	0732

which picks out the numbers from the tokenized string. Then you can do what you want with the numbers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources