 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / December 2006DECIMAL 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  Signature-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- | In A World Without Fences Who Needs Gates? | | Experience Linux. | -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- Gregor Kovac( wrote: > Hi! > [quoted text clipped - 5 lines] > 0000.01 > which is not nice. STRIP/TRIM will do what you need in DB2 9 Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab WAIUG Conference http://www.iiug.org/waiug/present/Forum2006/Forum2006.html 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. 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 Gregor Kovac wrote: > Hi! > [quoted text clipped - 5 lines] > 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 > Gregor Kovac wrote: > > Hi! [quoted text clipped - 30 lines] > > /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 [...] > 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 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 oh, there're negative numbers:) "Tonkuma 写道： " > This will be more simple than my previous post.. > ------------------------------ Commands Entered [quoted text clipped - 10 lines] > --------- > 0.01 I mean for a general .... "Hardy 写道： " > oh, there're negative numbers:) > [quoted text clipped - 14 lines] > > --------- > > 0.01 > 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? [...] > 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 >> 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. Signature-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- | In A World Without Fences Who Needs Gates? | | Experience Linux. | -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- 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! > [quoted text clipped - 13 lines] > | Experience Linux. | > -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.