| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-17-09, 15:47
|
|
Registered User
|
|
Join Date: Jan 2009
Posts: 5
|
|
|
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!
|
|
01-17-09, 16:37
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
What result do you want?
For example:
+2.0E+0
+3E-12
|
|
01-17-09, 16:39
|
|
Registered User
|
|
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)
|
|
01-18-09, 07:03
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
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/
|
|
01-18-09, 08:44
|
|
Registered User
|
|
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:
Double to Varchar
However there wasn't a really satisfying solution, I'm wondering if it's really that hard on db2?
|
|
01-18-09, 11:22
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
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/
|
|
01-18-09, 11:30
|
|
Registered User
|
|
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
|
|
01-18-09, 11:52
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
Quote:
|
Originally Posted by srynoname
I've meanwhile also found this older thread:
Double to Varchar
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/
|
|
01-18-09, 12:04
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
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
|
|
01-18-09, 12:21
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
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/
|
|
01-18-09, 12:37
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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.
|
|
01-23-09, 17:02
|
|
Registered User
|
|
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!
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Convert double to varchar
