By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,287 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Feb 5 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Feb 5, 3:06 pm, "apattin" <apat...@gmail.comwrote:
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

Feb 5 '07 #2

P: n/a
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
Feb 6 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.