Post reply

How to remove leading zeros?

  • July 25, 2003 at 9:21 am

    #467103

    quote:

    this should do it:

    cast(left(annoying_column,patindex('%ed%',annoying_column) - 1) as integer)

    you can either cast the other column as an integer, or throw another cast around this whole thing to get it back to character data.

    juppdiwupp, but this assumes only one number following 'ed', right?

    No, the PATINDEX finds the beginning of 'ed' in the column--meaning, the index of the 'e'.

    The -1 is just to subtract one from that value, so that the LEFT function will get everything up to the 'e', not everything up to and including the 'e'. But there could be a thousand characters after 'ed' and it would still work.

  • July 25, 2003 at 9:27 am

    #467104

    quote:

    No, the PATINDEX finds the beginning of 'ed' in the column--meaning, the index of the 'e'.

    The -1 is just to subtract one from that value, so that the LEFT function will get everything up to the 'e', not everything up to and including the 'e'. But there could be a thousand characters after 'ed' and it would still work.

    But if 'ed' isnt in the string then the LEFT function is giving an error, since patindex will return 0. Doing a LEFT(str, -1) gives the error. yes?

  • July 25, 2003 at 9:28 am

    #467106

    I think DavidT got it...

    LEFT JOIN DataPaqMFG ON convert(varchar(15),(replicate('0', (15 - len(DataPaqMFG.TD_PART))))) + cast(DataPaqMFG.TD_PART as varchar(15)) = convert(varchar(15),(replicate('0', (15 - len(products.PART_NUM))))) + cast(products.PART_NUM as varchar(15))

    Can anyone find a reason this would fail? It seems to work for me.

    Thanks again

  • July 25, 2003 at 1:24 pm

    #467137

    Hi CarKnee,

    you've already seem to have found a solution.

    What about REPLACE (your_column, 'ed','',1) and CAST this as int?

    Haven't tried this here at home, but when it works, it should be faster because of less elementar operations

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 25, 2003 at 4:11 pm

    #467156

    quote:

    Hi CarKnee,

    you've already seem to have found a solution.

    What about REPLACE (your_column, 'ed','',1) and CAST this as int?

    Haven't tried this here at home, but when it works, it should be faster because of less elementar operations

    Cheers,

    Frank

    Hrmmm.. In none of the examples did I see an actual zero, other than the leading zeros. Why not just do a replace on the zero's to nothing?

    from table1

    left outer join table2

    on replace(table1.col1,'0','') = table2.col1

    I'm making a big assumption though.


    -Ken

  • July 28, 2003 at 12:14 am

    #467202

    quote:

    Hrmmm.. In none of the examples did I see an actual zero, other than the leading zeros. Why not just do a replace on the zero's to nothing?

    from table1

    left outer join table2

    on replace(table1.col1,'0','') = table2.col1

    I'm making a big assumption though.

    looking again at the provided sample data, that's a ggod point!

    Looks fairly simpler than

    LEFT JOIN DataPaqMFG ON convert(varchar(15),(replicate('0', (15 - len(DataPaqMFG.TD_PART))))) + cast(DataPaqMFG.TD_PART as varchar(15)) = convert(varchar(15),(replicate('0', (15 - len(products.PART_NUM))))) + cast(products.PART_NUM as varchar(15))

    Let's hope it works on all data

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 28, 2003 at 5:46 am

    #467235

    There can be zeros in the middle of the number. I just didnt give it as an example.

    Thanks.

Viewing 7 posts - 16 through 21 (of 21 total)

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