Solved

Get just int portion of alphanumeric string

Posted on 2011-03-16
3
570 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:mrichmon
  • 2
3 Comments
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
Comment Utility
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
 
LVL 35

Author Comment

by:mrichmon
Comment Utility
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
 
LVL 14

Expert Comment

by:Daniel_PL
Comment Utility
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now