Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to convert decimal data type to character with no leading zeros?

    Hi,
    on db2 v8.2 I have the following table:
    Code:
    COL1   DECIMAL   ( 9, 4)
    COL2   DECIMAL   ( 7, 2)
    COL3   DECIMAL   ( 9, 2)
    COL4   DECIMAL   ( 7, 3)
    COL5   DECIMAL   ( 7, 2)
    SELECT COL1, COL2, COL3, COL4, COL5 FROM MYTABLE returns:
    Code:
       0,0000      0,00        0,00     0,000      0,00
       1,0000   1000,00     2000,00     0,000      0,00
       0,1000    522,00        0,00     0,000     76,00
       0,0100    700,00        0,00     0,000    150,00
       0,0300   1040,00        0,00     0,000     76,00
    SELECT CHAR(COL1), CHAR(COL2), CHAR(COL3), CHAR(COL4), CHAR(COL5) FROM MYTABLE returns:
    Code:
    00000.0000  00000.00  0000000.00  0000.000  00000.00
    00001.0000  01000.00  0002000.00  0000.000  00000.00
    00000.1000  00522.00  0000000.00  0000.000  00076.00
    00000.0100  00700.00  0000000.00  0000.000  00150.00
    00000.0300  01040.00  0000000.00  0000.000  00076.00
    How to convert decimal data types to char to get spaces instead of leading zeros? So just like first select statement output but data types converted to character.
    Regards

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    grofaty, I don't believe there is any single function that will do what you want. From what you say and show, your requirements are:

    Remove/Replace all leading 0 except if it is the last 0 before the decimal point in which case the one 0 should remain. Also the output needs to be Right Justified.

    You don't mention anything about a sign. Positive numbers normally do show a + sign but if you have any Negative numbers you will have the - to deal with. Do you expect negative numbers?

    This can probably be done with a combination of functions but it would take a little while to develop.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Take a look at locate & substr
    Dave

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Varchar(COLi) -- that it !

    select varchar(0001.33) from sysibm.sysdummy1
    result ==> 1.33

    Lenny

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    VARCHAR(number data type) is supported on DB2 9.7 for LUW.
    It is not supported on db2 v8.2 for LUW.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The original request was
    How to convert decimal data types to char to get spaces instead of leading zeros?
    varchar(0001.33) removes leading zeros.

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up

    Quote Originally Posted by tonkuma View Post
    The original request was

    varchar(0001.33) removes leading zeros.
    Thank you, tonkuma !

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    The original request mentions replacing leading zeros with spaces but the example shows one leading 0 left if that is the only number to the left of the decimal point. Also, the example shows right justified output. If both of these are required, then varchar (by itself in V9.7) won't meet this.

    I am hoping grofaty will reply with what is required and/or acceptable.

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up Common solution

    This simple query solve all problems:

    Code:
    select strip(substr(char(colK), 1, 1)) 
           || ifnull(repeat(' ', nullif(length(char(abs(colK))) - 
                                 length(varchar(abs(colK))), 0) - 1), '')
           || varchar(abs(colK))
    from sysibm.sysdummy1
    compare:

    Code:
    select strip(substr(char(0001.33), 1, 1)) 
           || ifnull(repeat(' ', nullif(length(char(0001.33)) - 
                                 length(varchar(0001.33)), 0) - 1), '')
           || varchar(0001.33)
    from sysibm.sysdummy1
    and

    Code:
    select strip(substr(char(-0001.33), 1, 1)) 
           || ifnull(repeat(' ', nullif(length(char(0001.33)) - 
                                 length(varchar(0001.33)), 0) - 1), '')
           || varchar(0001.33)
    from sysibm.sysdummy1
    Lenny
    Last edited by Lenny77; 01-20-10 at 12:58.

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    @Stealth_DBA, for my case I don't need negative numbers.

    @Lenny77, I am using DB2 v8.2 where the following functions are not available: strip, ifnull, nullif and varchar. Just for the case of
    curiosity I have tried your sql on v9.7 and got error: "SQL0440N No authorized routine named "IFNULL" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884"
    Last edited by grofaty; 01-21-10 at 03:11.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           SUBSTR( RIGHT( '    '   || RTRIM(CHAR(INT(col1))) , 5 ) || RIGHT(RTRIM(CHAR(col1)) , 5) , 1 , 10 ) AS col1
         , SUBSTR( RIGHT( '    '   || RTRIM(CHAR(INT(col2))) , 5 ) || RIGHT(RTRIM(CHAR(col2)) , 3) , 1 ,  8 ) AS col2
         , SUBSTR( RIGHT( '      ' || RTRIM(CHAR(INT(col3))) , 7 ) || RIGHT(RTRIM(CHAR(col3)) , 3) , 1 , 10 ) AS col3
         , SUBSTR( RIGHT( '   '    || RTRIM(CHAR(INT(col4))) , 4 ) || RIGHT(RTRIM(CHAR(col4)) , 4) , 1 ,  8 ) AS col4
         , SUBSTR( RIGHT( '    '   || RTRIM(CHAR(INT(col5))) , 5 ) || RIGHT(RTRIM(CHAR(col5)) , 3) , 1 ,  8 ) AS col5
      FROM table_grof;
    ------------------------------------------------------------------------------
    
    COL1       COL2     COL3       COL4     COL5    
    ---------- -------- ---------- -------- --------
        0.0000     0.00       0.00    0.000     0.00
        1.0000  1000.00    2000.00    0.000     0.00
        0.1000   522.00       0.00    0.000    76.00
        0.0100   700.00       0.00    0.000   150.00
        0.0300  1040.00       0.00    0.000    76.00
    
      5 record(s) selected.

  12. #12
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Red face

    Quote Originally Posted by grofaty View Post
    @Stealth_DBA, for my case I don't need negative numbers.

    @Lenny77, I am using DB2 v8.2 where the following functions are not available: strip, ifnull, nullif and varchar. Just for the case of
    curiosity I have tried your sql on v9.7 and got error: "SQL0440N No authorized routine named "IFNULL" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884"
    Is it DB2, or something else ? Maybe it's a plain English ?

    Lenny

  13. #13
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by tonkuma View Post
    How about this?
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           SUBSTR( RIGHT( '    '   || RTRIM(CHAR(INT(col1))) , 5 ) || RIGHT(RTRIM(CHAR(col1)) , 5) , 1 , 10 ) AS col1
         , SUBSTR( RIGHT( '    '   || RTRIM(CHAR(INT(col2))) , 5 ) || RIGHT(RTRIM(CHAR(col2)) , 3) , 1 ,  8 ) AS col2
         , SUBSTR( RIGHT( '      ' || RTRIM(CHAR(INT(col3))) , 7 ) || RIGHT(RTRIM(CHAR(col3)) , 3) , 1 , 10 ) AS col3
         , SUBSTR( RIGHT( '   '    || RTRIM(CHAR(INT(col4))) , 4 ) || RIGHT(RTRIM(CHAR(col4)) , 4) , 1 ,  8 ) AS col4
         , SUBSTR( RIGHT( '    '   || RTRIM(CHAR(INT(col5))) , 5 ) || RIGHT(RTRIM(CHAR(col5)) , 3) , 1 ,  8 ) AS col5
      FROM table_grof;
    ------------------------------------------------------------------------------
    
    COL1       COL2     COL3       COL4     COL5    
    ---------- -------- ---------- -------- --------
        0.0000     0.00       0.00    0.000     0.00
        1.0000  1000.00    2000.00    0.000     0.00
        0.1000   522.00       0.00    0.000    76.00
        0.0100   700.00       0.00    0.000   150.00
        0.0300  1040.00       0.00    0.000    76.00
    
      5 record(s) selected.
    Hi,
    almost there. Your report is returning "." as decimal separator, but in my locale should return ",". See my first post. This is an very old bug in DB2 when converting dec-->char it ignores locale.

    I could use replace(your_string, '.', ','), but I get output back to varchar. It looks one more conversion is needed.
    Regards

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that CHAR(col1) would result decimal separator corresponding to locale.
    If decimal separator should return "," in your locale, you can specify it explicitly, like this...
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           SUBSTR( RIGHT( '    '   || RTRIM(CHAR(INT(col1))) , 5 ) || ',' || RIGHT(DIGITS(col1) , 4) , 1 , 10 ) AS col1
         , SUBSTR( RIGHT( '    '   || RTRIM(CHAR(INT(col2))) , 5 ) || ',' || RIGHT(DIGITS(col2) , 2) , 1 ,  8 ) AS col2
         , SUBSTR( RIGHT( '      ' || RTRIM(CHAR(INT(col3))) , 7 ) || ',' || RIGHT(DIGITS(col3) , 2) , 1 , 10 ) AS col3
         , SUBSTR( RIGHT( '   '    || RTRIM(CHAR(INT(col4))) , 4 ) || ',' || RIGHT(DIGITS(col4) , 3) , 1 ,  8 ) AS col4
         , SUBSTR( RIGHT( '    '   || RTRIM(CHAR(INT(col5))) , 5 ) || ',' || RIGHT(DIGITS(col5) , 2) , 1 ,  8 ) AS col5
      FROM table_grof;
    ------------------------------------------------------------------------------
    
    COL1       COL2     COL3       COL4     COL5    
    ---------- -------- ---------- -------- --------
        0,0000     0,00       0,00    0,000     0,00
        1,0000  1000,00    2000,00    0,000     0,00
        0,1000   522,00       0,00    0,000    76,00
        0,0100   700,00       0,00    0,000   150,00
        0,0300  1040,00       0,00    0,000    76,00
    
      5 record(s) selected.
    Last edited by tonkuma; 01-23-10 at 07:33. Reason: Used DIGITS

  15. #15
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Stupid query including good idea

    This stupid query has to work on your enviroment:

    Code:
    select substr(char(-0000000001234587.89), 1, 1) 
    || case 
    when posstr (translate(substr(char(-0000000001234587.89), 2), 
           '.........', '123456789'), '.') = 1
    then substr(char(-0000000001234587.89), 2)
    else 
    replace(substr(char(-0000000001234587.89), 2,  
           posstr(translate(substr(char(-0000000001234587.89), 2), 
                 '.........', '123456789'), '.') - 1), '0', ' ')
    end  
    || 
    substr(char(-0000000001234587.89),  
           posstr(translate(substr(char(-0000000001234587.89), 2), 
                 '.........', '123456789'), '.') + 1) 
    from sysibm.sysdummy1
    Result:
    -1234587.89
    Lenny
    Last edited by Lenny77; 01-22-10 at 12:28.

Posting Permissions

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