Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extract Number From Alphanumeric Character Expand / Collapse
Author
Message
Posted Tuesday, December 30, 2008 10:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, December 31, 2008 12:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:28 PM
Points: 173, Visits: 1,705
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
Posted Wednesday, December 31, 2008 1:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
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 »

Add to briefcase

Permissions Expand / Collapse