Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2007
    Posts
    56

    Unanswered: Convert Varchar into Double

    Hello,

    can any body let me know as to How can a varchar can be converted into double.The varchar field contains a value which can be converted to double ex 21.34,34.50 I have used the cast function but no luck.
    SET xxannua=CAST(xxannual as double);
    where xxannua is double type, but i am getting a message that cannot convert varchar to double. can somebody tell me as how should i go about it.

    Thanks.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by donraja_ht
    SET xxannua=CAST(xxannual as double);
    i am getting a message that cannot convert varchar to double.
    Didn't check if this works, but maybe it helps to first convert to DECIMAL:
    Code:
    SET xxannua=CAST(CAST(xxannual AS DEC(10,2) 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/

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Peter, you will run into problems if the numeric value doesn't fit into DEC(10, 2) but is a valid DOUBLE value.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What platform and version/release of DB2 are you using?
    Looking into manuals, I felt that DB2 for z/OS V8 and DB2 for iSeries V5R4 support CAST(string AS double).
    So, I guessed that you are using DB2 for LUW.

    DB2 for LUW has function SYSFUN.DOUBLE(schema SYSFUN is the point).
    Please try:
    SET xxannua = DOUBLE(xxannual);

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is my test result on DB2 for LUW 9.5.
    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.5.2
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    VALUES DOUBLE('1.1E2');
    ------------------------------------------------------------------------------
    
    1                       
    ------------------------
      +1.10000000000000E+002
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    VALUES CAST('1.1E2' AS DOUBLE);
    ------------------------------------------------------------------------------
    SQL0461N  A value with data type "SYSIBM.VARCHAR" cannot be CAST to type 
    "SYSIBM.DOUBLE".  SQLSTATE=42846

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    For those of you prefering the use of CAST, the following 2-step cast will also work:
    Code:
    cast(cast(xxannual as decfloat) as double)
    According to the conversion table (p.92 of the SQL Reference guide 1), the only two datatypes that CHAR and VARCHAR cannot be converted to are exactly DOUBLE and FLOAT.
    Which explains the need to go through either DECIMAL (in case you know it's a fixed-decimal-point numeric value) or through DECFLOAT (which supports e.g. '1.1e2'), or using the function DOUBLE(...)
    Last edited by Peter.Vanroose; 04-26-09 at 18:03.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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