Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: How to convert decimal(10,2) to STRING.

    Hi friends,

    Another things that was brought into my notice as below.

    Code:
    D:\TD>db2 select cast(real_chips as decimal(10,2)) from client_acc where acc_num = 'TT0529656034'
    
    REAL_CHIPS
    ------------
          105.00
    
      1 record(s) selected.
    
    
    D:\TD>db2 select char(cast(real_chips as decimal(10,2))) from client_acc where acc_num = 'TT0529656034'
    
    1
    ------------
    00000105.00
    
      1 record(s) selected.
    
    
    D:\TD>db2 select cast(real_chips as decimal(10,2)) from client_acc where acc_num = 'TT0529656034'
    
    REAL_CHIPS
    ------------
          105.00
    
      1 record(s) selected.
    
    
    D:\TD>db2 select real_chips from client_acc where acc_num = 'TT0529656034'
    
    REAL_CHIPS
    ------------
          105.00
    
      1 record(s) selected.
    
    
    D:\TD>db2 select cast(real_chips as char) from client_acc where acc_num = 'TT0529656034'
    
    1
    -
    0
    SQL0445W  Value "00000105.00 " has been truncated.  SQLSTATE=01004
    
    
      1 record(s) selected with 1 warning messages printed.
    
    
    D:\TD>db2 select cast(real_chips as char(10)) from client_acc where acc_num = 'TT0529656034'
    
    1
    ----------
    00000105.0
    SQL0445W  Value "00000105.00 " has been truncated.  SQLSTATE=01004
    
    
      1 record(s) selected with 1 warning messages printed.
    
    
    D:\TD>db2 select real_chips  from client_acc where acc_num = 'TT0529656034'
    
    REAL_CHIPS
    ------------
          105.00
    
      1 record(s) selected.
    
    
    D:\TD>db2 select ltrim(cast(real_chips as char))  from client_acc where acc_num = 'TT0529656034'
    
    1
    -
    0
    SQL0445W  Value "00000105.00 " has been truncated.  SQLSTATE=01004
    
    
      1 record(s) selected with 1 warning messages printed.
    The requirement was to convert REAL_CHIPS to character string form decimal(10,2).

    Any help ??

    Thanks
    DBFinder

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    DBFinder, Decimal(10,2) converted to character would need 10 characters for the Numbers, 1 for the decimal point and (if negative numbers) 1 for the sign.

    To be safe you should use:

    cast(real_chips as char(12))

    Note: one clue to the number of characters you need is the number of '-' between the column name and the value when you cast it as Decimal(10,2). In you case it is '------------' which is 12 dashes.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Code:
    C:\Program Files\IBM\SQLLIB\BIN>db2 select cast(real_chips as char(12)) from client_acc where acc_num='TT0529656034'
    
    1
    ------------
    00000105.00
    
      1 record(s) selected.
    So, Stealth_DBA , what is the difference.

    The point was to convert to character string. without left padding.

    Is there any DB2 function to get following result


    1
    ------------
    105.00
    The developer wants to use a scalar function in the query.

    DBFinder

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    It depends on which DB2 version you have. In DB2 9.5 there is a STRIP function.

    STRIP(string, L/T/B, strip-character)

    String is the item to be Stripped.
    L/T/B is on of Leading, Trailing or Both
    strip-character by default is space but you can specify other character(s)

    Assuming you don't have any negative numbers:

    STRIP( CAST( real_chips as CHAR(10) ), L, '0' )

    NOTE: that '0' is a zero.

    But it should really be:

    STRIP( CAST( real_chips as CHAR(11) ), L, '0' )

    since you could (technically) have 99999999.99 as a value (assuming no negatives) and that is 11 characters and truncation would occur.

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    C:\Program Files\IBM\SQLLIB\BIN>DB2 VALUES STRIP('00056',L,'0')
    SQL0206N "L" is not valid in the context where it is used. SQLSTATE=42703

    C:\Program Files\IBM\SQLLIB\BIN>db2level
    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09050" with
    level identifier "03010107".
    Informational tokens are "DB2 v9.5.0.808", "s071001", "NT3295", and Fix Pack
    "0".
    Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".
    I agree, but what is that = STRIP does not work ??

    DBFinder

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    DBFinder, try this in either Command Editor or Command Window:

    SELECT '000056', STRIP('000056',L,'0') FROM SYSIBM.SYSDUMMY1

    In a Command Window I ran:

    E:\Program Files\IBM\SQLLIB\BIN>DB2 SELECT '000056', STRIP('000056',L,'0') FROM SYSIBM.SYSDUMMY1

    1 2
    ------ ------
    000056 56
    My DB2LEVEL looks the same as yours:
    E:\Program Files\IBM\SQLLIB\BIN>db2level
    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09050" with
    level identifier "03010107".
    Informational tokens are "DB2 v9.5.0.808", "s071001", "NT3295", and Fix Pack
    "0".
    Product is installed at "E:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Code:
    C:\Program Files\IBM\SQLLIB\BIN>DB2 SELECT '000056', STRIP('000056',L,'0') FROM SYSIBM.SYSDUMMY1
    
    1      2
    ------ ------
    000056 56
    
      1 record(s) selected.
    That's what I did already !

    I was connecting to the database on Ver 8.2 server.


    So Strip is only for V 9.5

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    That is correct. STRIP is NOT in V8.2.

    Getting rid of leading zeroes without STRIP might still be possible but it won't be 'pretty'. Let me think about it (unless someone else has an idea).

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    This should work (assuming no negative numbers).

    REAL_CHIPS is CAST to a CHAR(11) and as SUBSTR is used with the value of REAL_CHIPS used to determine the Start and Length parameters.

    Code:
    SELECT SUBSTR(CAST(real_chips AS CHAR(11)), 
                   CASE 
                       WHEN real_chips <=         .99 THEN  9
                       WHEN real_chips <=        9.99 THEN  8
                       WHEN real_chips <=       99.99 THEN  7
                       WHEN real_chips <=      999.99 THEN  6
                       WHEN real_chips <=     9999.99 THEN  5
                       WHEN real_chips <=    99999.99 THEN  4
                       WHEN real_chips <=   999999.99 THEN  3
                       WHEN real_chips <=  9999999.99 THEN  2
                                                      ELSE  1
                   END, 
                   CASE 
                       WHEN real_chips <=         .99 THEN  3
                       WHEN real_chips <=        9.99 THEN  4
                       WHEN real_chips <=       99.99 THEN  5
                       WHEN real_chips <=      999.99 THEN  6
                       WHEN real_chips <=     9999.99 THEN  7
                       WHEN real_chips <=    99999.99 THEN  8
                       WHEN real_chips <=   999999.99 THEN  9
                       WHEN real_chips <=  9999999.99 THEN 10
                                                      ELSE 11
                   END)
    FROM SYSIBM.SYSDUMMY1
    ;
    It does NOT have a leading 0 for anything less than 1 (ex. .99) but that shouldn't be a problem.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Assumed that data type of real_chip is DECIMAL(10,2) and values of real_chip are not negative.

    REPLACE(LTRIM(REPLACE(CHAR(real_chip, 12), '0', ' ')), ' ', '0')
    or
    TRANSLATE(LTRIM(TRANSLATE(CHAR(real_chip, 12), ' ', '0')), '0', ' ')

    where CHAR(real_chip, 12) can be replaced by CAST(real_chip AS CHAR(12)).

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks tonkuma ,

    Actually I added RTRIM to strip a space on Right before translate.

    Good work. Thanks again

    Code:
    TRANSLATE(LTRIM(TRANSLATE(RTRIM(CHAR(REAL_CHIPS)), ' ', '0')), '0', ' ')
    DBFinder

Posting Permissions

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