Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Get just int portion of alphanumeric string

Posted on 2011-03-16
3
Medium Priority
?
581 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 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

877 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