469,582 Members | 2,386 Online

# DECIMAL into CHAR

Hi!

Let's have a data of type DECIMAL(6, 2) and with value 0.01.
How can I convert this one into a "pretty" CHAR?
I've tried
VALUES(CHAR(DECIMAL(0.01, 6, 2)))
and I get
0000.01
which is not nice.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Dec 16 '06 #1
13 22521 Gregor Kovac( wrote:
Hi!

Let's have a data of type DECIMAL(6, 2) and with value 0.01.
How can I convert this one into a "pretty" CHAR?
I've tried
VALUES(CHAR(DECIMAL(0.01, 6, 2)))
and I get
0000.01
which is not nice.
STRIP/TRIM will do what you need in DB2 9

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Dec 16 '06 #2
I assumed DECIMAL presision and scale are arbitrary chosen.
I can't imagine simpler than this. I feel that this way is too
strightforwards.
------------------------------ Commands Entered
------------------------------
VALUES SUBSTR('-',1,INT(1-SIGN(SIGN(DECIMAL(0.01, 6, 2))+1)))
||SUBSTR('0',1,INT(1-SIGN(SIGN(ABS(DECIMAL(0.01, 6, 2))-1)+1)))
||TRANSLATE(LTRIM(TRANSLATE(RTRIM(CHAR(ABS(DECIMAL (0.01, 6,
2)))),'','0')),'0',' ');
------------------------------------------------------------------------------

1
----------
0.01

Dec 16 '06 #3
If you simply used STRIP, '0' before decimal point('.') will be lost.
And if value is negative, I guess it is not so simple to add leading
minus sign('-').
(Perhaps, CASE expression, etc will be necessary)

------------------------------ Commands Entered
------------------------------
VALUES STRIP(CHAR(ABS(DECIMAL(0.01, 6, 2))),L,'0');
------------------------------------------------------------------------------

1
-----------
..01

1 record(s) selected.

Dec 16 '06 #4

Gregor Kovac wrote:
Hi!

Let's have a data of type DECIMAL(6, 2) and with value 0.01.
How can I convert this one into a "pretty" CHAR?
I've tried
VALUES(CHAR(DECIMAL(0.01, 6, 2)))
and I get
0000.01
which is not nice.
IMO formatting results should not be done in the database layer.
Anyhow, heres one attempt removing leading zeroes using a cte. You can
wrap it in a scalar function

[ltjn@lelles ~]\$ db2 "with t (s,n) as (VALUES(CHAR(DECIMAL(0.01, 6,
2)),0) union all select substr(s,2),n+1 from t where substr(s,2,1) =
'0' and n<100) select s from t where n = (select max(n) from t)"

S
--------
0.01

1 record(s) selected.
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Agreed :-)

/Lennart

Dec 16 '06 #5

Lennart wrote:
Gregor Kovac wrote:
Hi!

Let's have a data of type DECIMAL(6, 2) and with value 0.01.
How can I convert this one into a "pretty" CHAR?
I've tried
VALUES(CHAR(DECIMAL(0.01, 6, 2)))
and I get
0000.01
which is not nice.

IMO formatting results should not be done in the database layer.
Anyhow, heres one attempt removing leading zeroes using a cte. You can
wrap it in a scalar function

[ltjn@lelles ~]\$ db2 "with t (s,n) as (VALUES(CHAR(DECIMAL(0.01, 6,
2)),0) union all select substr(s,2),n+1 from t where substr(s,2,1) =
'0' and n<100) select s from t where n = (select max(n) from t)"

S
--------
0.01

1 record(s) selected.
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Agreed :-)

/Lennart
If initial value is more than 1, extra 0 will be remained.
------------------------------ Commands Entered
------------------------------
with t (s,n) as (VALUES(CHAR(DECIMAL(1.01, 6,
2)),0) union all select substr(s,2),n+1 from t where substr(s,2,1) =
'0' and n<100) select s from t where n = (select max(n) from t);
------------------------------------------------------------------------------

S
--------
01.01

And if initial value is 1020.01(more than 1000 and second digit is '0',
result will be wrong.
------------------------------ Commands Entered
------------------------------
with t (s,n) as (VALUES(CHAR(DECIMAL(1000.01, 6,
2)),0) union all select substr(s,2),n+1 from t where substr(s,2,1) =
'0' and n<100) select s from t where n = (select max(n) from t);
------------------------------------------------------------------------------

S
--------
020.01
To correct these two case, one idea is to modify following.
------------------------------ Commands Entered
------------------------------
with t (s,n) as (VALUES(CHAR(DECIMAL(0.01, 6,
2)),0) union all select substr(s,2),n+1 from t where substr(s,1,1) =
'0' and n<100)
select SUBSTR(s,CASE WHEN SUBSTR(s,2,1) = '.' OR n = 0 THEN 1 ELSE 2
END) S
from t where n = (select COALESCE(NULLIF(max(n)-1,-1),0) from t)
;
------------------------------------------------------------------------------

S
--------
0.01

------------------------------ Commands Entered
------------------------------
with t (s,n) as (VALUES(CHAR(DECIMAL(1.01, 6,
2)),0) union all select substr(s,2),n+1 from t where substr(s,1,1) =
'0' and n<100)
select SUBSTR(s,CASE WHEN SUBSTR(s,2,1) = '.' OR n = 0 THEN 1 ELSE 2
END) S
from t where n = (select COALESCE(NULLIF(max(n)-1,-1),0) from t)
;
------------------------------------------------------------------------------

S
--------
1.01

------------------------------ Commands Entered
------------------------------
with t (s,n) as (VALUES(CHAR(DECIMAL(1020.01, 6,
2)),0) union all select substr(s,2),n+1 from t where substr(s,1,1) =
'0' and n<100)
select SUBSTR(s,CASE WHEN SUBSTR(s,2,1) = '.' OR n = 0 THEN 1 ELSE 2
END) S
from t where n = (select COALESCE(NULLIF(max(n)-1,-1),0) from t)
;
------------------------------------------------------------------------------

S
--------
1020.01

Dec 17 '06 #6

Tonkuma wrote:
[...]
If initial value is more than 1, extra 0 will be remained.
[...]

Yes, you are right. At first sight I thought adding a dummy '0' at the
beginning of the initial string
would help

db2 "with t (s,n) as (VALUES('0' || CHAR(DECIMAL(1020.01, 6, 2)),0)
union all select substr(s,2),n+1 from t where substr(s,1,1) = '0' and
n<100) select s from t where n = (select max(n) from t)"

S
---------
1020.01

but we still need to handle the case with one leading '0'.

/Lennart

Dec 17 '06 #7
This will be more simple than my previous post..
------------------------------ Commands Entered
------------------------------
with t (s,n) as (VALUES(CHAR(DECIMAL(0.01, 6,
2)),0) union all select substr(s,2),n+1 from t where substr(s,1,1) =
'0' and n<100)
select SUBSTR('0'||s,CASE WHEN SUBSTR(s,1,1)='.' THEN 1 ELSE 2 END) S
from t where n = (select max(n) from t)
;
------------------------------------------------------------------------------

S
---------
0.01

Dec 17 '06 #8
oh, there're negative numbers:)

"Tonkuma Ð´µÀ£º
"
This will be more simple than my previous post..
------------------------------ Commands Entered
------------------------------
with t (s,n) as (VALUES(CHAR(DECIMAL(0.01, 6,
2)),0) union all select substr(s,2),n+1 from t where substr(s,1,1) =
'0' and n<100)
select SUBSTR('0'||s,CASE WHEN SUBSTR(s,1,1)='.' THEN 1 ELSE 2 END) S
from t where n = (select max(n) from t)
;
------------------------------------------------------------------------------

S
---------
0.01
Dec 18 '06 #9
I mean for a general ....

"Hardy Ð´µÀ£º
"
oh, there're negative numbers:)

"Tonkuma Ð´µÀ£º
"
This will be more simple than my previous post..
------------------------------ Commands Entered
------------------------------
with t (s,n) as (VALUES(CHAR(DECIMAL(0.01, 6,
2)),0) union all select substr(s,2),n+1 from t where substr(s,1,1) =
'0' and n<100)
select SUBSTR('0'||s,CASE WHEN SUBSTR(s,1,1)='.' THEN 1 ELSE 2 END) S
from t where n = (select max(n) from t)
;
------------------------------------------------------------------------------

S
---------
0.01
Dec 18 '06 #10
db2 =select * from decimal;

DEC
--------
1020.01
0.01
-0.01

db2 =with t(an,mark, lzero) as ( select strip(char(abs(dec)),l,'0'),
case when
dec < 0 then '-' else '' end case , case when abs(dec) < 1 then '0'
else '' end
case from decimal)
db2 (cont.) =select mark||lzero||an from t;

1
----------
1020.01
0.01
-0.01

"Gregor KovaÄ åéï¼
"
Hi!

Let's have a data of type DECIMAL(6, 2) and with value 0.01.
How can I convert this one into a "pretty" CHAR?
I've tried
VALUES(CHAR(DECIMAL(0.01, 6, 2)))
and I get
0000.01
which is not nice.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Dec 18 '06 #11
Lennart wrote:
IMO formatting results should not be done in the database layer.
Amen!

Why is it that developers always claim that the database HAS to return
formatted values?

Dec 18 '06 #12

Ian wrote:
Lennart wrote:
[...]
Why is it that developers always claim that the database HAS to return
formatted values?
They probably don't have the time to format the data, since they are to
busy writing business rules, that should have been declared in the
database :-)

/Lennart

Dec 18 '06 #13
Ian wrote:
Lennart wrote:
>IMO formatting results should not be done in the database layer.

Amen!

Why is it that developers always claim that the database HAS to return
formatted values?
Well, it wasn't the developers who made the decision, but the head of the
software group, so to speak. Don't ask me why, he just loves to have
everything in the database.

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Dec 19 '06 #14

### This discussion thread is closed

Replies have been disabled for this discussion.

### Similar topics

 7 posts views Thread by Philipp H. Mohr | last post: by 1 post views Thread by Jean-michel | last post: by 7 posts views Thread by Golan | last post: by 3 posts views Thread by Andres A. | last post: by 5 posts views Thread by trawlerman | last post: by 7 posts views Thread by billbaitsg | last post: by 8 posts views Thread by sdlt85 | last post: by 1 post views Thread by karanbikash | last post: by 3 posts views Thread by capoeira26 | last post: by reply views Thread by suresh191 | last post: by reply views Thread by goatbishop | last post: by reply views Thread by Trystan | last post: by reply views Thread by Marketing QM | last post: by 11 posts views Thread by MGadAllah | last post: by reply views Thread by allessa | last post: by reply views Thread by hefaz | last post: by reply views Thread by billypeterson | last post: by 4 posts views Thread by guiromero | last post: by