Solved

Get just int portion of alphanumeric string

Posted on 2011-03-16
3
574 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
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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 …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

832 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