locked
remove prepadded zeroes RRS feed

  • Question

  • How can I remove prepadded zeroes from the Column below, Please suggest

    [Column 0]
    0001323
    00000023232
    0000432332
    0000000053232
    00332
    000455
    
    Saturday, October 9, 2010 9:05 PM

Answers

  • Try:

    select
        c1,
        cast(cast(c1 as int) as varchar(25)),
        right(c1, len(c1) - (patindex('%[1-9]%', c1) - 1))
    from
        (
        values
            ('0001323'),
            ('00000023232'),
            ('0000432332'),
            ('0000000053232'),
            ('00332'),
            ('000455')
        ) as T(c1);

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Kalman Toth Thursday, October 14, 2010 12:28 PM
    Saturday, October 9, 2010 9:26 PM

All replies

  • Try

    select substring(ColumnName, patindex('%[^0]%',ColumnName), 10) 
    From <table>

     

    • Proposed as answer by Naomi N Sunday, October 10, 2010 1:19 AM
    • Edited by Chirag Shah Sunday, October 10, 2010 2:08 AM
    Saturday, October 9, 2010 9:12 PM
  • another way

    SELECT REPLACE(LTRIM(REPLACE(ColumnName, '0', ' ')), ' ', '0')
    From <table>
    
    Saturday, October 9, 2010 9:22 PM
  • Try:

    select
        c1,
        cast(cast(c1 as int) as varchar(25)),
        right(c1, len(c1) - (patindex('%[1-9]%', c1) - 1))
    from
        (
        values
            ('0001323'),
            ('00000023232'),
            ('0000432332'),
            ('0000000053232'),
            ('00332'),
            ('000455')
        ) as T(c1);

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Kalman Toth Thursday, October 14, 2010 12:28 PM
    Saturday, October 9, 2010 9:26 PM