String Tokening

• deepak-214010

  SSC Veteran

  Points: 252

  More actions

  March 13, 2005 at 11:44 pm

  #88284

  Hello,

  I am looking for SQL function which can provide me following result.

  Input Output

  ------------------------

  08 08

  08-Ent 08

  08Ent 08

  009Ent 009

  009-P 009

  08_P 08

  i.e.I want first integer in string before any thing non-integer(may be special char or alphabet)

  begins.I am using SQL Server.

  So please let me know is there any solution.

  -

• David Burrows

  SSC Guru

  Points: 65111

  More actions

  March 14, 2005 at 7:24 am

  #546811

  Not aware of a function, per se but you could try this

  SELECT CASE WHEN PATINDEX('%[^0-9]%',[Input])>0

  THEN LEFT([Input],PATINDEX('%[^0-9]%',[Input])-1)

  ELSE [Input]

  END

   

  Far away is close at hand in the images of elsewhere.
  Anon.

• David Burrows

  SSC Guru

  Points: 65111

  More actions

  March 14, 2005 at 7:27 am

  #546812

  Or this

  SELECT LEFT(@Input,PATINDEX('%[^0-9]%',@Input+'Z')-1)

  Far away is close at hand in the images of elsewhere.
  Anon.