remove leading 0's from a character string

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    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: 26752

    There's lots of ways, this should work

    declare @S varchar(20)

    set @S='008EE'

    --set @S='05TT'

    --set @S='000RTG'

    --set @S='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

    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 varchar(20)

    set @S='00000'

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

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

    Thanks [/font]

  • Ron McCullough

    SSC Guru

    Points: 63877

    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: 26752

    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 varchar(20)

    set @S='00000'

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

    DECLARE @S VARCHAR(20)

    --SET @S='008EE'

    --SET @S='05TT'

    --SET @S='000RTG'

    --SET @S='0GG'

    SET @S='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

    Stole your code, thanks!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply