remove leading 0's from a character string

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  November 9, 2009 at 12:35 pm

  #137264

  Hi,

  I have a character srting that has leading 0's,How can I get rif of those leading 0's from this character string

  for ex:

  008EE

  05TT

  000RTG

  0GG

  I cannot convert it to INT as it is a string.

  Any help?

  Thanks in Advance

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• Mark Cowne

  One Orange Chip

  Points: 26979

  More actions

  November 9, 2009 at 12:51 pm

  #1076882

  There's lots of ways, this should work

  declare @s-2 varchar(20)

  set @s-2='008EE'

  --set @s-2='05TT'

  --set @s-2='000RTG'

  --set @s-2='0GG'

  select substring(@s,patindex('%[^0]%',@s),len(@s))

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  November 9, 2009 at 1:55 pm

  #1076906

  Thanks a lot..

  it works fine for all the string ..but doesn't work for 0000 string

  it only removes one 0

  I want to make it single 0 if it is more than one 0

  declare @s-2 varchar(20)

  set @s-2='00000'

  select substring(@s,patindex('%[^0]%',@s),len(@s)) as strg

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  November 9, 2009 at 3:10 pm

  #1076965

  Is this what you need?

  IF Len(REPLACE(@s,'0','' )) > 0

  select substring(@s,patindex('%[^0]%',@s),len(@s))

  ELSE

  SELECT LEN(REPLACE(@s,'0','' ))

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Mark Cowne

  One Orange Chip

  Points: 26979

  More actions

  November 10, 2009 at 12:47 am

  #1077096

  SQL Learner-684602 (11/9/2009)

  ---

  Thanks a lot..

  it works fine for all the string ..but doesn't work for 0000 string

  it only removes one 0

  I want to make it single 0 if it is more than one 0

  declare @s-2 varchar(20)

  set @s-2='00000'

  select substring(@s,patindex('%[^0]%',@s),len(@s)) as strg

  DECLARE @s-2 VARCHAR(20)

  --SET @s-2='008EE'

  --SET @s-2='05TT'

  --SET @s-2='000RTG'

  --SET @s-2='0GG'

  SET @s-2='0000'

  SELECT SUBSTRING(@s, COALESCE(NULLIF(PATINDEX('%[^0]%',@s),0),LEN(@s)) ,LEN(@s))

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• John Hanrahan

  Hall of Fame

  Points: 3825

  More actions

  November 1, 2013 at 12:07 pm

  #1663554

  Stole your code, thanks!