Remove Leading Zeros in any situation T-SQL

  • Comments posted to this topic are about the item Remove Leading Zeros in any situation T-SQL

  • Unfortunately, if the text includes 0, this method will fail.

    😎

    declare @String varchar(30)
    select @String = 'X01TEST00001234'
    select
    case
    when ISNUMERIC(@String) = 0 then SUBSTRING (@String,0,CHARINDEX('0',@String)) + SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')))
    else SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')))
    end

    will return

    X1TEST00001234
  • @ Venugopal Saride,

    I have to say that is IS a very interesting way to try to do this but I have to agree with Eirikur...  It doesn't meet the promises of "any situation" or "all leading zeros".  It also doesn't allow for the actual value of zero even if there are no leading zeros for that.  Example follows:

    declare @String varchar(30)
    select @String = 'Test0'
    select
    case
    when ISNUMERIC(@String) = 0
    then SUBSTRING (@String,0,CHARINDEX('0',@String)) + SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')))
    else SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')))
    end

    Result:

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • neither does it deal with scientific values where the whole string IS the value

     

    declare @String varchar(30)
    select @String = '1E02'
    select
    try_convert(float, @string)
    ,case
    when ISNUMERIC(@String) = 0 then SUBSTRING (@String,0,CHARINDEX('0',@String)) + SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')))
    else SUBSTRING(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),''), PATINDEX('%[^0]%', REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')+'.'), LEN(REPLACE(@String,SUBSTRING (@String,0,CHARINDEX('0',@String)),'')))
    end

     

Viewing 4 posts - 1 through 3 (of 3 total)

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