DECIMAL into CHAR

[Gregor =?UTF-8?B?S292YcSN?=]

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Serge Rielau]

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

[Tonkuma]

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

[Tonkuma]

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.

[Lennart]

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

[Tonkuma]

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

[Lennart]

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

[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

[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

[Hardy]

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

[Hardy]

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[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?

[Lennart]

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

[Gregor =?UTF-8?B?S292YcSN?=]

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-