# Thread: Formatting the decimal string

1. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

## Unanswered: Formatting the decimal string

The following query give you the easiest way to format the decimal string:

Code:
```with input (number) as
(select 1123167890.4327 from sysibm.sysdummy1
)
,
transform_1 (num, part, rem, trans, fract, k ) as
(select number, int(0), int(number), varchar('', 150), number - int(number), 12
from input
union all
select
number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)),
strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then ''
when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
substr(digits(int(rem / power(10, k - 3))), 8, 3)
else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
end
|| case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
, fract, k - 3
from transform_1, input
where k - 3 >= 0
)
select num "Input.................", trans "Result"from transform_1 tr where k = 0```
So we'll get result:

Input................. Result
1123167890.4327 1,123,167,890.4327
Lenny
Last edited by Lenny77; 09-09-09 at 12:05.

2. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
Needs to be an UDF.

3. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Originally Posted by Marcus_A
Needs to be an UDF.
I am 100% agree with you.

Everybody can make UDF using this query.

It' s very usable in reports. Users appreciate it.

Lenny

4. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

## modified formula

I have modified the previous formula because I did not like the results where
number < 1 (0.5 or .5 formatted as .5 which is also not bad, but I prefer to
have the result 0.5

Now query looks like:

Code:
```with input (number) as
(select -123456789.012345 from sysibm.sysdummy1
)
,
transform_1 (num, part, rem, trans, fract, k ) as
(select number, int(0), int(abs(number)), varchar('', 150),
abs(number) - int(abs(number)), 12
from input where abs(number) >= 1000
union all
select
number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)),
strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then  ''
when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
case when number < 0 then '-' else '' end
|| strip(digits(int(rem / power(10, k - 3))), l, '0')
else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
end
|| case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
, fract, k - 3
from transform_1, input
where k - 3 >= 0 and abs(number) >= 1000
union all
select number, int(0), int(0), varchar(number), number, 0
from input
where abs(number) < 1000
)
select num "Input.................",
trans "Result"
from transform_1 tr where k = 0```
Lenny
Last edited by Lenny77; 09-09-09 at 15:47.

5. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
With this complete formula-SQL we'll get result (for number = -.012345) :

Input....... Result
-.012345 -0.012345

6. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Two issues.
1) SQL0347W.
2) It's not work well for negative input.

Code:
```------------------------------ Commands Entered ------------------------------
with input (number) as
(select -1123167890.4327 from sysibm.sysdummy1
)
,
transform_1 (num, part, rem, trans, fract, k ) as
(select number, int(0), int(number), varchar('', 150), number - int(number), 12
from input where number > 1
union all
select
number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)),
strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then ''
when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
substr(digits(int(rem / power(10, k - 3))), 8, 3)
else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
end
|| case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
, fract, k - 3
from transform_1, input
where k - 3 >= 0 and number > 1
union all
select number, int(0), int(0), varchar(number), number, 0
from input
where number < 1
)
select num, trans from transform_1 tr where k = 0;
------------------------------------------------------------------------------

NUM              TRANS
---------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
SQL0347W  The recursive common table expression "DB2ADMIN.TRANSFORM_1" may
contain an infinite loop.  SQLSTATE=01605

-1123167890.4327 -1123167890.4327

1 record(s) selected with 1 warning messages printed.```

7. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Originally Posted by tonkuma
Two issues.
1) SQL0347W.
2) It's not work well for negative input.

Code:
```------------------------------ Commands Entered ------------------------------
with input (number) as
(select -1123167890.4327 from sysibm.sysdummy1
)
,
transform_1 (num, part, rem, trans, fract, k ) as
(select number, int(0), int(number), varchar('', 150), number - int(number), 12
from input where number > 1
union all
select
number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)),
strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then ''
when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
substr(digits(int(rem / power(10, k - 3))), 8, 3)
else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
end
|| case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
, fract, k - 3
from transform_1, input
where k - 3 >= 0 and number > 1
union all
select number, int(0), int(0), varchar(number), number, 0
from input
where number < 1
)
select num, trans from transform_1 tr where k = 0;
------------------------------------------------------------------------------

NUM              TRANS
---------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
SQL0347W  The recursive common table expression "DB2ADMIN.TRANSFORM_1" may
contain an infinite loop.  SQLSTATE=01605

-1123167890.4327 -1123167890.4327

1 record(s) selected with 1 warning messages printed.```
I am sorry tonkuma !

I just corrected the formula. Please use the last one.

Let me know on issue.

Lenny

8. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

## Corrected Formula

This formula working good for all known to me cases:

Code:
```with input (number) as
(select -123456789.012345 from sysibm.sysdummy1
)
,
transform_1 (num, part, rem, trans, fract, k ) as
(select number, int(0), int(abs(number)), varchar('', 150),
abs(number) - int(abs(number)), 12
from input where abs(number) >= 1000
union all
select
number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)),
strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then  ''
when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
case when number < 0 then '-' else '' end
|| strip(digits(int(rem / power(10, k - 3))), l, '0')
else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
end
|| case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
, fract, k - 3
from transform_1, input
where k - 3 >= 0 and abs(number) >= 1000
union all
select number, int(0), int(0), varchar(number), number, 0
from input
where abs(number) < 1000
)
select num "Input.................",
trans "Result"
from transform_1 tr where k = 0```
Lenny

9. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Still SQL0347W was issued.
(I tested on DB2 9.7 on Windows.)

To supress the warning message, I changed "where k - 3 >= 0 ..." to "where k >= 3 ...".

10. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
DB2 9.7 for LUW supports VARCHAR_FORMAT ( decimal-floating-point-expression [, format-string] ).

Code:
```------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------

Database Connection Information

Database server        = DB2/NT 9.7.0
Local database alias   = SAMPLE

A JDBC connection to the target has succeeded.
------------------------------ Commands Entered ------------------------------
SELECT dec_nbr
, VARCHAR( VARCHAR_FORMAT(dec_nbr, '999,999,999,999.9999'), 30 ) formatted
FROM (VALUES 1123167890.4327, 123167890.4327, 23167890.4327, 3167890.4327, 0.5
, -1123167890.4327, -123167890.4327, -23167890.4327, -3167890.4327, -0.5) s(dec_nbr);
------------------------------------------------------------------------------

DEC_NBR          FORMATTED
---------------- ------------------------------
1123167890.4327    1,123,167,890.4327
123167890.4327      123,167,890.4327
23167890.4327       23,167,890.4327
3167890.4327        3,167,890.4327
0.5000                0.5000
-1123167890.4327   -1,123,167,890.4327
-123167890.4327     -123,167,890.4327
-23167890.4327      -23,167,890.4327
-3167890.4327       -3,167,890.4327
-0.5000               -0.5000

10 record(s) selected.```

11. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Ok tonkuma !

In my enviroment it's working with any case.

I tried to use
Code:
`where k >= 3`
it's working too.

Thanks, Lenny

12. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Originally Posted by tonkuma
DB2 9.7 for LUW supports VARCHAR_FORMAT ( decimal-floating-point-expression [, format-string] ).

Code:
```------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------

Database Connection Information

Database server        = DB2/NT 9.7.0
Local database alias   = SAMPLE

A JDBC connection to the target has succeeded.
------------------------------ Commands Entered ------------------------------
SELECT dec_nbr
, VARCHAR( VARCHAR_FORMAT(dec_nbr, '999,999,999,999.9999'), 30 ) formatted
FROM (VALUES 1123167890.4327, 123167890.4327, 23167890.4327, 3167890.4327, 0.5
, -1123167890.4327, -123167890.4327, -23167890.4327, -3167890.4327, -0.5) s(dec_nbr);
------------------------------------------------------------------------------

DEC_NBR          FORMATTED
---------------- ------------------------------
1123167890.4327    1,123,167,890.4327
123167890.4327      123,167,890.4327
23167890.4327       23,167,890.4327
3167890.4327        3,167,890.4327
0.5000                0.5000
-1123167890.4327   -1,123,167,890.4327
-123167890.4327     -123,167,890.4327
-23167890.4327      -23,167,890.4327
-3167890.4327       -3,167,890.4327
-0.5000               -0.5000

10 record(s) selected.```
Good to you !
I am working with v.8, where I don't have this function.

Lenny

13. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Even for guys which are such lucky as tonkuma and can use VARCHAR_FORMAT function it could be interesting because it shown how it works.

Lenny

14. Registered User
Join Date
Jul 2009
Posts
150
This is a man !

15. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Here is Sample UDFs for DB2 UDB V8.
Sample UDFs for Migration

You may be interested in:
FnToChrN.txt TO_CHAR
Returns character string expression of decimal number according to specified format.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•