Convert Varchar into Double

donraja_ht · #1 (**permalink**) 04-26-09, 05:22

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.

Peter.Vanroose · #2 (**permalink**) 04-26-09, 06:19

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);

stolze · #3 (**permalink**) 04-26-09, 12:39

Peter, you will run into problems if the numeric value doesn't fit into DEC(10, 2) but is a valid DOUBLE value.

tonkuma · #4 (**permalink**) 04-26-09, 13:59

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);

tonkuma · #5 (**permalink**) 04-26-09, 14:13

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

Peter.Vanroose · #6 (**permalink**) 04-26-09, 16:56

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(...)

Internet Services

Web Development

Digital Marketing

Consumer Tech