Extract Number From Alphanumeric Character

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Extract Number From Alphanumeric Character

Extract Number From Alphanumeric Character

Rate Topic

Display Mode

Topic Options

Author

Message

jchandramouli

Posted Tuesday, December 30, 2008 10:46 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, December 20, 2010 4:47 AM
Points: 42, Visits: 141

Hi,

I got a table .

Like this,
declare @tbSample table(Code VARCHAR(10)

Some records as follows.
insert into tbSample select 'SDH00151'
insert into tbSample select 'SDH00152'
insert into tbSample select 'SDH00153'

How to extract the numbers from each row?

P.N:The data format will always be XXXNNNNN (X- Char N- Numeric).

Thanks.

"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin

Post #627814

Eric Klovning

Posted Wednesday, December 31, 2008 12:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 05, 2013 10:09 PM
Points: 173, Visits: 1,621

If the format is always 3 char followed by 5 numeric can you just get the right 5 characters?

DECLARE @tbSample TABLE(Code VARCHAR(10))

INSERT INTO @tbSample SELECT 'SDH00151'
INSERT INTO @tbSample SELECT 'SDH00152'
INSERT INTO @tbSample SELECT 'SDH00153'

SELECT
Code
,RIGHT(Code,5) AS CodeNum
FROM
@tbSample

Post #627843

Madhivanan-208264

Posted Wednesday, December 31, 2008 1:30 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:17 AM
Points: 329, Visits: 461

For diffrent length
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

Post #627880

« Prev Topic | Next Topic »

Permissions