| |
|
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.
|
 |
04-26-10, 14:57
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 7
|
|
|
Simple Numeric Formatting
|
|
Hi,
With this example
SELECT 3000 MY_NBR FROM SYSIBM.SYSDUMMY1
How can I get the output of MY_NBR to include the comma thousands separator like 3,000?
|
|
04-26-10, 15:22
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
How you see the data is a function of what you are using, not DB2. If you are using the CLP, then numbers are just that--numbers. If you want a comma, then YOU need to convert it with some function (UDF).
Andy
|
|
04-26-10, 15:46
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 7
|
|
|
|
Was that supposed to be helpful?
|
|
04-26-10, 16:55
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
DB2 has no mechanism built in that will format the number the way you want. If you want it displayed differently, then you have to convert it. I am sorry if you think this is not helpful, but that is the way it is.
Andy
|
|
04-26-10, 16:59
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 7
|
|
Thank you Andy, your second response was helpful!
Not what I wanted of course, but at least I know I can quit searching for a formatting function and create my own.
|
|
04-26-10, 17:11
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Nothing is new under Moon
Quote:
Originally Posted by WindInFace
Hi,
With this example
SELECT 3000 MY_NBR FROM SYSIBM.SYSDUMMY1
How can I get the output of MY_NBR to include the comma thousands separator like 3,000?
|
You can find the answers on your question here:
Formatting the decimal string
Where I together with tonkuma made the different models of this process.
Lenny
|
|
04-27-10, 16:40
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
No recursive SQL at all
Solution without recursive SQL (you can use the part of the main SELECT, depending on the maximum length of your number):
Code:
select
case length(integer_part)
when 3
then sign || strip(integer_part, l, '0') || fraction
when 6
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || fraction
when 9
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || fraction
when 12
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || fraction
when 15
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || fraction
when 18
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || ','
|| substr(integer_part, 16, 3) || fraction
when 21
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || ','
|| substr(integer_part, 16, 3) || ','
|| substr(integer_part, 19, 3) || fraction
when 24
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || ','
|| substr(integer_part, 16, 3) || ','
|| substr(integer_part, 19, 3) || ','
|| substr(integer_part, 22, 3) || fraction
when 27
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || ','
|| substr(integer_part, 16, 3) || ','
|| substr(integer_part, 19, 3) || ','
|| substr(integer_part, 22, 3) || ','
|| substr(integer_part, 25, 3) || fraction
when 30
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || ','
|| substr(integer_part, 16, 3) || ','
|| substr(integer_part, 19, 3) || ','
|| substr(integer_part, 22, 3) || ','
|| substr(integer_part, 25, 3)|| ','
|| substr(integer_part, 28, 3) || fraction
when 33
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || ','
|| substr(integer_part, 16, 3) || ','
|| substr(integer_part, 19, 3) || ','
|| substr(integer_part, 22, 3) || ','
|| substr(integer_part, 25, 3) || ','
|| substr(integer_part, 28, 3) || ','
|| substr(integer_part, 31, 3) || fraction
end formatted_number
from (
select
case when mod(length(integer_part), 3) >= 1
then repeat('0', 3 - mod(length(integer_part), 3)) || integer_part
else integer_part
end integer_part,
fraction, sign
from
(
select
case when posstr(in_chr_nbr, '.') > 1
then strip(substr(in_chr_nbr, 1, posstr(in_chr_nbr, '.') - 1))
else strip(in_chr_nbr)
end integer_part,
case when posstr(in_chr_nbr, '.') > 1
then substr(in_chr_nbr, posstr(in_chr_nbr, '.'))
else ''
end fraction,
sign
from
(select
case when input_nbr >= 0
then varchar(input_nbr)
else substr(varchar(input_nbr), 2)
end in_chr_nbr,
case when input_nbr >= 0 then ''
else '-'
end sign
from (select -3000111222333444555666777888.999 input_nbr
from sysibm.sysdummy1) N1 ) N2 ) N3 ) N4
Result:
Quote:
FORMATTED_NUMBER
-3,000,111,222,333,444,555,666,777,888.999
|
Lenny
|
|
04-28-10, 13:09
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Improved formula. Doesn't use recursive SQL.
Improved formula doesn't use recursive SQL:
Code:
select
case
when integer_part <= '000'
then sign || '0' || fraction
else
case
length(integer_part)
when 3
then sign || strip(integer_part, l, '0') || fraction
when 6
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || fraction
when 9
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || fraction
when 12
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || fraction
when 15
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || fraction
when 18
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || ','
|| substr(integer_part, 16, 3) || fraction
when 21
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || ','
|| substr(integer_part, 16, 3) || ','
|| substr(integer_part, 19, 3) || fraction
when 24
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || ','
|| substr(integer_part, 16, 3) || ','
|| substr(integer_part, 19, 3) || ','
|| substr(integer_part, 22, 3) || fraction
when 27
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || ','
|| substr(integer_part, 16, 3) || ','
|| substr(integer_part, 19, 3) || ','
|| substr(integer_part, 22, 3) || ','
|| substr(integer_part, 25, 3) || fraction
when 30
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || ','
|| substr(integer_part, 16, 3) || ','
|| substr(integer_part, 19, 3) || ','
|| substr(integer_part, 22, 3) || ','
|| substr(integer_part, 25, 3) || ','
|| substr(integer_part, 28, 3) || fraction
when 33
then sign || strip(substr(integer_part, 1, 3), l, '0') || ','
|| substr(integer_part, 4, 3) || ','
|| substr(integer_part, 7, 3) || ','
|| substr(integer_part, 10, 3) || ','
|| substr(integer_part, 13, 3) || ','
|| substr(integer_part, 16, 3) || ','
|| substr(integer_part, 19, 3) || ','
|| substr(integer_part, 22, 3) || ','
|| substr(integer_part, 25, 3) || ','
|| substr(integer_part, 28, 3) || ','
|| substr(integer_part, 31, 3) || fraction
end end formatted_number
from (
select
case when mod(length(integer_part), 3) >= 1
then repeat('0', 3 - mod(length(integer_part), 3)) || integer_part
else integer_part
end integer_part,
fraction, sign
from
(
select
case when posstr(in_chr_nbr, '.') > 1
then strip(substr(in_chr_nbr, 1, posstr(in_chr_nbr, '.') - 1))
else strip(in_chr_nbr)
end integer_part,
case when posstr(in_chr_nbr, '.') > 1
then strip(substr(in_chr_nbr, posstr(in_chr_nbr, '.')), t, '.')
else ''
end fraction,
sign
from
(select
case when input_nbr >= 0
then varchar(0 + input_nbr)
else substr(varchar(0 + input_nbr), 2)
end in_chr_nbr,
case when input_nbr >= 0 then ''
else '-'
end sign
from (select -3000111222333444555666777888.123 input_nbr
from sysibm.sysdummy1) N1 ) N2 ) N3 ) N4;
Result the same:
Quote:
FORMATTED_NUMBER
-3,000,111,222,333,444,555,666,777,888.123
|
Lenny
|
|
04-28-10, 16:42
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
Alright Lenny let's see you show off now. How about in written format? For instance:
one million, three hundred ninety-five thousand, one hundred twenty-two.
Dave
|
|
04-28-10, 17:03
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
You can do it
Quote:
Originally Posted by dav1mo
Alright Lenny let's see you show off now. How about in written format? For instance:
one million, three hundred ninety-five thousand, one hundred twenty-two.
Dave
|
How you know that's easy !
Lenny
|
|
04-28-10, 23:05
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 150
|
|
-3,000,111,222,333,444,555,666,777,888.123
Quote:
Originally Posted by dav1mo
Alright Lenny let's see you show off now. How about in written format? For instance:
one million, three hundred ninety-five thousand, one hundred twenty-two.
Dave
|
Can you read the number -3,000,111,222,333,444,555,666,777,888.123 for me ?
Thank you. Kara.
|
Last edited by DB2Plus; 04-28-10 at 23:08.
|
| 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
>
Simple Numeric Formatting
