Stripping alpha - QQ for George / lessthandot.com GURUS

[lmctech]

I found the link and it's GREAT, but I have a spin for ya:
I copied and pasted it and it worked great, with one exception: when there is a hyphen at the end of the string it is not removing it.

Here is what I used for the SQL:
Select
appnumber,
TB24,
len(tb24) as lenTB24,
Left(SubString(tb24, PatIndex('%[0-9.-]%', tb24), 8000),
PatIndex('%[^0-9.-]%', SubString(tb24, PatIndex('%[0-9.-]%', tb24), 8000) + 'X')-1) as Modified_TB24

from custom
where appnumber >0
and isnull(tb24,'X')<> 'X'


Here is my output, check out line 3:
1116087 10 702 EXP Ca 702
1096757 10 694 exp sc 694
817899 8 710- EXP 710-
832010 7 713 EXP 713

Any ideas?

 

[lmctech]

NOTE:
Of course, after posting I found a way to fix my problem and am posting it here for sharing.
The way to get rid of the hyphen at the end of the string is by manipulating the second PATINDEX, like so:
PatIndex('%[^0-9.]%'

The full statement is shown below.

Select
appnumber,
TB24,
len(tb24) as lenTB24,
Left(SubString(tb24, PatIndex('%[0-9.-]%', tb24), 8000),
PatIndex('%[^0-9.]%', SubString(tb24, PatIndex('%[0-9.-]%', tb24), 8000) + 'X')-1) as Modified_TB24

from custom
where appnumber >0
and isnull(tb24,'X')<> 'X'

 

[gmmastros]

You're welcome.

Just for completeness sake, I assume you are referring to this blog:

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

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom