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

[apattin]

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

[jefftyzzer]

On Feb 5, 3:06 pm, "apattin" <[e-mail]:

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

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

[Knut Stolze]

apattin wrote:

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.

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
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany