Tidy up data in field, need function ?

• Digs

  SSCertifiable

  Points: 7333

  More actions

  November 20, 2005 at 3:10 pm

  #188599

  Hi,

  I have a SKU field that is TEXT

  In this field I can get data like this

  000000045654

  0000789

  0000000456

  KJGKG

  00HFJJ

  I need to remove all front ZEROs by a function, and return the data as TEXT, any ideas on how to..

  Like

  45654

  789

  456

  KJGKG

  HFJJ

   

• ramses2nd

  Hall of Fame

  Points: 3720

  More actions

  November 21, 2005 at 8:17 am

  #604980

  Off the top of my head, this would probably work:

  CONVERT(TEXT,REPLACE(fieldname,'0',''))

  FROM tablename

• philcart

  SSC-Forever

  Points: 47795

  More actions

  November 21, 2005 at 2:52 pm

  #605079

  I'm thinking you'd use something like PATINDEX to locate the first non-zero character and then SUBSTRING from there.

  DECLARE @MyVar varchar(30)
  SET @MyVar = '000000045654'
  SELECT SUBSTRING(@MyVar, PATINDEX('%[^0]%', @MyVar), LEN(@MyVar))

  --------------------
  Colt 45 - the original point and click interface