Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Unanswered: Convert double to varchar

    Hello,

    can somebody please help me, how to convert a double value to a varchar or char value without leading zeros and such things?

    Thank you very much!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What result do you want?
    For example:
    +2.0E+0
    +3E-12

  3. #3
    Join Date
    Jan 2009
    Posts
    5
    hello,
    thanks for your reply!

    i want the "normal" decimal form e.g. 1600.00 ( as varchar or char)

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by srynoname
    how to convert a double value to a varchar or char value without leading zeros and such things?
    What about CAST(expr AS VARCHAR(255)) ?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Jan 2009
    Posts
    5
    hello peter,
    thanks for your reply. while you should expect it is that easy (i also really expected this before opening this thread), it isn't. your way just gives me
    SQLState: 56098
    ErrorCode: -727

    I've meanwhile also found this older thread:
    http://www.dbforums.com/db2/1622322-double-varchar.html
    However there wasn't a really satisfying solution, I'm wondering if it's really that hard on db2?

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by srynoname
    your way just gives me
    SQLState: 56098
    ErrorCode: -727
    According to the manuals, a -727 has to do with an "implicit system action" of some sort (indicated by the "action type" that comes with this SQLcode).
    So the cast is perfectly valid, but you are using it in a context (maybe a host variable?) where some other action (maybe a second, implicit cast?) causes an error.
    In which environment are you trying to do the CAST(expr AS varchar(n)) ? What is your platform and DB2 version?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Jan 2009
    Posts
    5
    hello peter,

    thank you again for your reply.
    i've not seen that there was a second error code before the -727 error code.
    the code before is
    SQLState: 42846
    ErrorCode: -461
    and -461 is for the cast, it cannot cast from sourcetype (double) to targettype (varchar) ):

    i guess you should be able to reproduce this.

    system / db version:
    getDatabaseProductName = DB2/LINUXX8664
    SQL09053 = SQL09053

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by srynoname
    I've meanwhile also found this older thread:
    http://www.dbforums.com/db2/1622322-double-varchar.html
    However there wasn't a really satisfying solution, I'm wondering if it's really that hard on db2?
    In general, a DOUBLE cannot be converted to a (VAR)CHAR without too much trailing zeros.
    E.g., writing 5.0E20 as a decimal text would give 500000000000000000000.
    Hence, the best systematic thing DB2 can do when casting to text is to write CAST(5.0E20 AS VARCHAR(255)) as '5.0E20'. B.t.w., the same holds for
    CAST(DOUBLE(500000000000000000000) AS VARCHAR(255)).
    How to "force" a normal decimal textual representation? Well, the only DB2 datatype that has such a representation is DECIMAL(m,n). Casting to such a datatype will do what you want:
    CAST(5.0E20 AS DECIMAL(20,0)) will cast to decimal; next casting this to VARCHAR(255) will give you the long, readable expansion.

    If you have a 100% certain idea about the range of the data, say you know that the DOUBLE data is always integer and has at most 7 digits, cast it first to DECIMAL(7,0) then to VARCHAR(255), but this will leave the leading zero's.
    In this particular example, though, cast it first to INT then to VARCHAR(9) and you're where you want to be!
    Now suppose you know that the values always have at most two digits after the decimal point and 7 before. Or better said, you want to "ignore" the 3rd etc. digits after the decimal point.
    You could do the following to get a "most readable" format:
    Code:
     CAST(0.01*CAST(expr*100 AS INT) AS VARCHAR(255))
    This would not work for more than 9 significant digits, of course. Unless your version of DB2 has BIGINT.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I am not really answering your question, but why do you want to use such a conversion/cast in the first place? I would recommend you work with DOUBLE values in your application to avoid the loss of precision inherent to such conversions. That is typically the safest course of action - everything else is asking for trouble.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by srynoname
    i guess you should be able to reproduce this.
    system / db version:
    getDatabaseProductName = DB2/LINUXX8664
    SQL09053 = SQL09053
    OK, on Linux a cast from double to varchar indeed seems to be impossible.
    I did my experiments on DB2 for z/OS, where this cast is possible.
    So try out my INT or BIGINT cast suggestion, or follow Knut's suggestion to keep the data as DOUBLE.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Just for fun.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT n
         , CAST(
           CASE WHEN n < 0 THEN '-' ELSE '' END ||
           TRANSLATE(
                     STRIP(
                           TRANSLATE(
                                     REPLACE(
                                             INSERT(
                                                    CAST(
                                                           REPEAT('0',308)
                                                         || REPLACE(SUBSTR(abs_char,1,POSSTR(abs_char,'E')-1),'.','')
                                                         || REPEAT('0',308)
                                                         AS VARCHAR(700))
                                                    ,INTEGER(SUBSTR(abs_char,POSSTR(abs_char,'E')+1))+310
                                                    ,0
                                                    ,'.')
                                            ,'0.','*.')
                                    ,' ', '0')
                          ,B)
                    ,'00', ' *')
               AS VARCHAR(330)) AS varchar_330
      FROM (SELECT n
                 , CAST(ABS(n) AS CHAR(30)) abs_char
             FROM (VALUES
                    1234500000000.
                  , 1.
                  , .1
                  , .01
                  , 1.7976931348623158e+308
                  , 0.0022250738585072014e-305
                  , 0
                  , -1
                  , -.1
                  , -.012345
                  , -1.7976931348623158e+308
                  , -2.2250738585072123e-308
                  ) N(n)
           ) D
    ;
    ------------------------------------------------------------------------------
    
    N                        VARCHAR_330                                                                                                                                                                                                                                                                                                                               
    ------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      +1.23450000000000E+012 1234500000000.                                                                                                                                                                                                                                                                                                                            
      +1.00000000000000E+000 1.                                                                                                                                                                                                                                                                                                                                        
      +1.00000000000000E-001 0.1                                                                                                                                                                                                                                                                                                                                       
      +1.00000000000000E-002 0.01                                                                                                                                                                                                                                                                                                                                      
      +1.79769313486232E+308 179769313486232000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.                    
      +2.22507385850720E-308 0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000022250738585072       
      +0.00000000000000E+000 0.                                                                                                                                                                                                                                                                                                                                        
      -1.00000000000000E+000 -1.                                                                                                                                                                                                                                                                                                                                       
      -1.00000000000000E-001 -0.1                                                                                                                                                                                                                                                                                                                                      
      -1.23450000000000E-002 -0.012345                                                                                                                                                                                                                                                                                                                                 
      -1.79769313486232E+308 -179769313486232000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.                   
      -2.22507385850721E-308 -0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000222507385850721     
    
      12 record(s) selected.

  12. #12
    Join Date
    Jan 2009
    Posts
    5
    Thank you all for your replies, it was an exercise I had to do (direct SQL, no application) so I had to convert it using SQL/DB2. At the end I left this one out, anyways this thread propably will be helpful in the feature, not only for me, but also for others users. So thank you all again for your work!

Posting Permissions

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