How to convert a DOUBLE to a string with no decimal separators?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

apattin - 05 Feb 2007 23:06 GMT

Hi all,

I have a table containing MEASURE DOUBLE. I want to

SELECT measure FROM mytable WHERE <condition>

If measure = 1860.45, I get: '1,860.45'. How can I strip the comma? I
tried:

DECIMAL(measure,20,5) still has the comma

CHAR(DECIMAL(measure,20,5)) gives me 000000000001860.48000

strip(char(decimal(measure,20,5)),LEADING,'0') gives me error 440 ("No
authorized routine named 'STRIP' of type 'FUNCTION' having compatible
arguments...)

There must be a way, but I cannot find it. Note that I do not really
know the magnitude of the numbers, thus the 20 and the 5, to make it
"big enough". Also, I read in other postings that DB2 may use the
locale information to format the number; I cannot change the locale so
I must do the formatting after the number is returned by DB2 unless
someone knows better.

Thanks for any help,

Alejandrina

Reply to this Message

jefftyzzer - 05 Feb 2007 23:42 GMT

> Hi all,
>
[quoted text clipped - 23 lines]
>
> Alejandrina

Will "SELECT DIGITS(measure) FROM mytable WHERE <condition>" work for
you? I'm not sure what client tool you're using (just CLP?), but I
wonder if it's responsible for putting in the comma, not DB2 per se.

--Jeff

Reply to this Message

Knut Stolze - 06 Feb 2007 12:08 GMT

> Hi all,
>
[quoted text clipped - 19 lines]
> I must do the formatting after the number is returned by DB2 unless
> someone knows better.

I believe that's an environment configuration thing in your environment.
Check the character that is set as 1000-separator in your regional
settings.

$ db2 "values double(1234567.90)"

1
------------------------
+1.23456790000000E+006

$ db2 "values decimal(double(1234567.90), 10, 2)"

1
------------
1234567.90

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message