Get just int portion of alphanumeric string

In SQL 2008
I have a column (varchar(5)) whose data looks like this:

001
200B
009AH
98

The alpha part always should come at the end of the string.

I need to just grab/extract the integer portion of the string or do something else so that I can use it in the following case statement:

DECLARE @NumberPortion int
SET @NumberPortion =  ????

RETURN CASE
          WHEN (@NumberPortion BETWEEN 1 AND 99) AND Field2 < 12 THEN 0
          WHEN (@NumberPortion BETWEEN 100 AND 199) AND Field2 < 8 THEN 0
          WHEN (@NumberPortion >= 200) AND Field2 < 4 THEN 0
          ELSE 1.5
      END

Open in new window

LVL 35
mrichmonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
You can do something like this:
DECLARE @data varchar(1000)
SET @data = '0001Abc';
BEGIN
WHILE PATINDEX('%[^0-9]%',@data)> 0
SET @data = REPLACE(@data,SUBSTRING(@data,PATINDEX('%[^0-9]%',@data),1),'')
END
SELECT @data

Open in new window


I'm showing you example, adapt it to your code :)
0
 
mrichmonAuthor Commented:
Perfect thanks - I could do this in C#, but have not done much with this type of pattern matching in SQL (I am fixing an error in a program written by a developer who left) so I appreciate the help.

This looks like it will remove any non-digit characters no matter where they are located - not just those at the end of the string correct?

I just needed to add a cast at the end

DECLARE @NumberPortion int
SET @NumberPortion =  CAST(@data AS int)


Thanks!
0
 
Daniel_PLDB Expert/ArchitectCommented:
PATINDEX gives you position of first non numerical value, then in loop it is removing each letter by replacing it with '' - nothing.
This code will turn your string into only numbers :)


DECLARE @data varchar(1000)
SET @data = '0xxz23cz1xc0a0331A2a2z3bc76as7d2s5a4';
BEGIN
WHILE PATINDEX('%[^0-9]%',@data)> 0
SET @data = REPLACE(@data,SUBSTRING(@data,PATINDEX('%[^0-9]%',@data),1),'')
END
SELECT @data

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.