Solved

Get just int portion of alphanumeric string

Posted on 2011-03-16
3
578 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35149997
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
ID: 35150165
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
ID: 35150331
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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