| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
01-20-10, 07:20
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 1,570
|
|
|
How to convert decimal data type to character with no leading zeros?
|
|
Hi,
on db2 v8.2 I have the following table:
Code:
COL1 DECIMAL ( 9, 4)
COL2 DECIMAL ( 7, 2)
COL3 DECIMAL ( 9, 2)
COL4 DECIMAL ( 7, 3)
COL5 DECIMAL ( 7, 2)
SELECT COL1, COL2, COL3, COL4, COL5 FROM MYTABLE returns:
Code:
0,0000 0,00 0,00 0,000 0,00
1,0000 1000,00 2000,00 0,000 0,00
0,1000 522,00 0,00 0,000 76,00
0,0100 700,00 0,00 0,000 150,00
0,0300 1040,00 0,00 0,000 76,00
SELECT CHAR(COL1), CHAR(COL2), CHAR(COL3), CHAR(COL4), CHAR(COL5) FROM MYTABLE returns:
Code:
00000.0000 00000.00 0000000.00 0000.000 00000.00
00001.0000 01000.00 0002000.00 0000.000 00000.00
00000.1000 00522.00 0000000.00 0000.000 00076.00
00000.0100 00700.00 0000000.00 0000.000 00150.00
00000.0300 01040.00 0000000.00 0000.000 00076.00
How to convert decimal data types to char to get spaces instead of leading zeros? So just like first select statement output but data types converted to character.
Regards
|
|
01-20-10, 09:37
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
grofaty, I don't believe there is any single function that will do what you want. From what you say and show, your requirements are:
Remove/Replace all leading 0 except if it is the last 0 before the decimal point in which case the one 0 should remain. Also the output needs to be Right Justified.
You don't mention anything about a sign. Positive numbers normally do show a + sign but if you have any Negative numbers you will have the - to deal with. Do you expect negative numbers?
This can probably be done with a combination of functions but it would take a little while to develop.
|
|
01-20-10, 09:39
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
|
|
Take a look at locate & substr
Dave
|
|
01-20-10, 10:42
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Varchar(COLi) -- that it !
select varchar(0001.33) from sysibm.sysdummy1
result ==> 1.33
Lenny
|
|
01-20-10, 10:49
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
VARCHAR(number data type) is supported on DB2 9.7 for LUW.
It is not supported on db2 v8.2 for LUW.
|
|
01-20-10, 10:53
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
The original request was
Quote:
|
How to convert decimal data types to char to get spaces instead of leading zeros?
|
varchar(0001.33) removes leading zeros.
|
|
01-20-10, 11:06
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Quote:
Originally Posted by tonkuma
The original request was
varchar(0001.33) removes leading zeros.
|
Thank you, tonkuma !
|
|
01-20-10, 11:13
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
The original request mentions replacing leading zeros with spaces but the example shows one leading 0 left if that is the only number to the left of the decimal point. Also, the example shows right justified output. If both of these are required, then varchar (by itself in V9.7) won't meet this.
I am hoping grofaty will reply with what is required and/or acceptable.
|
|
01-20-10, 11:36
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Common solution
This simple query solve all problems: 
Code:
select strip(substr(char(colK), 1, 1))
|| ifnull(repeat(' ', nullif(length(char(abs(colK))) -
length(varchar(abs(colK))), 0) - 1), '')
|| varchar(abs(colK))
from sysibm.sysdummy1
compare:
Code:
select strip(substr(char(0001.33), 1, 1))
|| ifnull(repeat(' ', nullif(length(char(0001.33)) -
length(varchar(0001.33)), 0) - 1), '')
|| varchar(0001.33)
from sysibm.sysdummy1
and
Code:
select strip(substr(char(-0001.33), 1, 1))
|| ifnull(repeat(' ', nullif(length(char(0001.33)) -
length(varchar(0001.33)), 0) - 1), '')
|| varchar(0001.33)
from sysibm.sysdummy1
Lenny
|
Last edited by Lenny77; 01-20-10 at 11:58.
|
01-21-10, 02:04
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 1,570
|
|
@Stealth_DBA, for my case I don't need negative numbers.
@Lenny77, I am using DB2 v8.2 where the following functions are not available: strip, ifnull, nullif and varchar. Just for the case of
curiosity I have tried your sql on v9.7 and got error: "SQL0440N No authorized routine named "IFNULL" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884"
|
Last edited by grofaty; 01-21-10 at 02:11.
|
01-21-10, 02:58
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
How about this?
Code:
------------------------------ Commands Entered ------------------------------
SELECT
SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col1))) , 5 ) || RIGHT(RTRIM(CHAR(col1)) , 5) , 1 , 10 ) AS col1
, SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col2))) , 5 ) || RIGHT(RTRIM(CHAR(col2)) , 3) , 1 , 8 ) AS col2
, SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col3))) , 7 ) || RIGHT(RTRIM(CHAR(col3)) , 3) , 1 , 10 ) AS col3
, SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col4))) , 4 ) || RIGHT(RTRIM(CHAR(col4)) , 4) , 1 , 8 ) AS col4
, SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col5))) , 5 ) || RIGHT(RTRIM(CHAR(col5)) , 3) , 1 , 8 ) AS col5
FROM table_grof;
------------------------------------------------------------------------------
COL1 COL2 COL3 COL4 COL5
---------- -------- ---------- -------- --------
0.0000 0.00 0.00 0.000 0.00
1.0000 1000.00 2000.00 0.000 0.00
0.1000 522.00 0.00 0.000 76.00
0.0100 700.00 0.00 0.000 150.00
0.0300 1040.00 0.00 0.000 76.00
5 record(s) selected.
|
|
01-21-10, 11:30
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Quote:
Originally Posted by grofaty
@Stealth_DBA, for my case I don't need negative numbers.
@Lenny77, I am using DB2 v8.2 where the following functions are not available: strip, ifnull, nullif and varchar. Just for the case of
curiosity I have tried your sql on v9.7 and got error: "SQL0440N No authorized routine named "IFNULL" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884"
|
 Is it DB2, or something else ? Maybe it's a plain English ?
Lenny
|
|
01-22-10, 05:58
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 1,570
|
|
Quote:
Originally Posted by tonkuma
How about this?
Code:
------------------------------ Commands Entered ------------------------------
SELECT
SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col1))) , 5 ) || RIGHT(RTRIM(CHAR(col1)) , 5) , 1 , 10 ) AS col1
, SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col2))) , 5 ) || RIGHT(RTRIM(CHAR(col2)) , 3) , 1 , 8 ) AS col2
, SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col3))) , 7 ) || RIGHT(RTRIM(CHAR(col3)) , 3) , 1 , 10 ) AS col3
, SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col4))) , 4 ) || RIGHT(RTRIM(CHAR(col4)) , 4) , 1 , 8 ) AS col4
, SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col5))) , 5 ) || RIGHT(RTRIM(CHAR(col5)) , 3) , 1 , 8 ) AS col5
FROM table_grof;
------------------------------------------------------------------------------
COL1 COL2 COL3 COL4 COL5
---------- -------- ---------- -------- --------
0.0000 0.00 0.00 0.000 0.00
1.0000 1000.00 2000.00 0.000 0.00
0.1000 522.00 0.00 0.000 76.00
0.0100 700.00 0.00 0.000 150.00
0.0300 1040.00 0.00 0.000 76.00
5 record(s) selected.
|
Hi,
almost there. Your report is returning "." as decimal separator, but in my locale should return ",". See my first post. This is an very old bug in DB2 when converting dec-->char it ignores locale.
I could use replace(your_string, '.', ','), but I get output back to varchar. It looks one more conversion is needed.
Regards
|
|
01-22-10, 09:42
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
I thought that CHAR(col1) would result decimal separator corresponding to locale.
If decimal separator should return "," in your locale, you can specify it explicitly, like this...
Code:
------------------------------ Commands Entered ------------------------------
SELECT
SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col1))) , 5 ) || ',' || RIGHT(DIGITS(col1) , 4) , 1 , 10 ) AS col1
, SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col2))) , 5 ) || ',' || RIGHT(DIGITS(col2) , 2) , 1 , 8 ) AS col2
, SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col3))) , 7 ) || ',' || RIGHT(DIGITS(col3) , 2) , 1 , 10 ) AS col3
, SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col4))) , 4 ) || ',' || RIGHT(DIGITS(col4) , 3) , 1 , 8 ) AS col4
, SUBSTR( RIGHT( ' ' || RTRIM(CHAR(INT(col5))) , 5 ) || ',' || RIGHT(DIGITS(col5) , 2) , 1 , 8 ) AS col5
FROM table_grof;
------------------------------------------------------------------------------
COL1 COL2 COL3 COL4 COL5
---------- -------- ---------- -------- --------
0,0000 0,00 0,00 0,000 0,00
1,0000 1000,00 2000,00 0,000 0,00
0,1000 522,00 0,00 0,000 76,00
0,0100 700,00 0,00 0,000 150,00
0,0300 1040,00 0,00 0,000 76,00
5 record(s) selected.
|
Last edited by tonkuma; 01-23-10 at 06:33.
Reason: Used DIGITS
|
01-22-10, 11:15
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Stupid query including good idea
This stupid query has to work on your enviroment:
Code:
select substr(char(-0000000001234587.89), 1, 1)
|| case
when posstr (translate(substr(char(-0000000001234587.89), 2),
'.........', '123456789'), '.') = 1
then substr(char(-0000000001234587.89), 2)
else
replace(substr(char(-0000000001234587.89), 2,
posstr(translate(substr(char(-0000000001234587.89), 2),
'.........', '123456789'), '.') - 1), '0', ' ')
end
||
substr(char(-0000000001234587.89),
posstr(translate(substr(char(-0000000001234587.89), 2),
'.........', '123456789'), '.') + 1)
from sysibm.sysdummy1
Result:
Lenny
|
Last edited by Lenny77; 01-22-10 at 11:28.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
How to convert decimal data type to character with no leading zeros?
