469,582 Members | 2,386 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,582 developers. It's quick & easy.

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.
I hope someone show more elegant way.
------------------------------ 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

3 条记录已选择。

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

3 条记录已选择。

"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
Ian
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
3 posts views Thread by Andres A. | last post: by
7 posts views Thread by billbaitsg | last post: by
1 post views Thread by karanbikash | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.