Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    1

    Question Unanswered: Leading zero / problem

    Hello SQL-People

    I need to export a File from db2.
    '000012.55' I need to trim the leading zero bye spaces.
    '.......12.55' (.=Blank) should be the result.
    Not '12.55'
    How can I do this??

    I'm a beginner an I tested with trim trailing and leading etc.

    I'll very happy for an example or any Help.

    Thanks.
    C.R.

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    select cast(colname as decimal(10,2))
    ...

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Sorry, I've re-read this post and you wanted to replace the leading zeroes with space padding.

    I can't think of a particularly elgant way to do this. Annoyingly for this problem, if you cast a decimal to a char to do some string manipulation, you will reintroduce leading zeroes.

    I've come up with this but there might be an easier way that i've overlooked...

    select right ( ' '
    concat rtrim (
    char(
    int(
    substr(
    col1
    ,1
    ,locate('.',col1)-1
    )
    )
    )
    )
    concat '.'
    concat substr(
    col1
    ,locate('.',col1)+1
    )
    , length (col1)
    )

    Basically, strip out the numeric portions of your string either side of the decimal point.
    Cast the first portion to an int to lose the leading zeroes and trim it to lose trailing spaces.
    Concatanate the 2 strings, reintroducing a '.' character to separate.
    Conctanate a string of spaces (of arbitary length) to the begininning of the nested string and take the right portion of the entire string to the length of the original string that contained leading zeroes.

    Not very pretty I know. There's probably an easier way but it eludes me at present.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •