Extract Number From Alphanumeric Character

• jchandramouli

  SSCommitted

  Points: 1772

  More actions

  December 30, 2008 at 10:46 pm

  #97028

  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

• Eric

  SSCommitted

  Points: 1803

  More actions

  December 31, 2008 at 12:01 am

  #919459

  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

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  December 31, 2008 at 1:30 am

  #919489

  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