| |
|
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.
|
 |
|
04-04-09, 15:47
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 1,829
|
|
|
|
1) As danglauser and Peter.Vanroose wrote,
DB2 for z/OS(and DB2 for iSeries) support cast to VARCHAR from DECIMAL.
I found same descriptions on "DB2 SQL Reference for z/OS" and "DB2 SQL Reference for iSeries".
Quote:
VARCHAR
.....
.....
Decimal to Varchar
.....
..... Leading zeros are not included. If the argument is negative, the result
begins with a minus sign. Otherwise, the result begins with a digit.
.....
|
But, DB2 for LUW(including version 9.5) doesn't suppot cast to VARCHAR from DECIMAL.
2) Nitya's expression(nitya_original) returns two decimal points for negative value.
And I think that a decimal function and two outer char functions are not neccesary.
I tried to fix the issue(nitya_modified). But, I couldn't do it(minus sign is not displayed for 0 > field_value > -1).
So, I tried other expressions(strip_a, strip_b and strip_c).
Code:
------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------
Database Connection Information
Database server = DB2/NT 9.5.2
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
A JDBC connection to the target has succeeded.
------------------------------ Commands Entered ------------------------------
SELECT d
, CHAR(RTRIM(char(integer(d))) || '.'
|| char(substr(char(decimal(d,17,2)),17))) nitya_original
, RTRIM(char(integer(d))) || '.' || substr(char(ABS(d)),17) nitya_modified
, CASE WHEN d < 0 THEN '-' ELSE '' END
|| CASE INT(d) WHEN 0 THEN '0' ELSE '' END
|| STRIP(CHAR(ABS(d)),L,'0') strip_a
, SUBSTR('-0',1+SIGN(INT(SIGN(d))+1),2-ABS(SIGN(INT(d)))-SIGN(INT(SIGN(d))+1))
|| STRIP(CHAR(ABS(d)),L,'0') strip_b
, RTRIM(SUBSTR('-0 ',2+SIGN(INT(d*2-1+6e-17)),2-ABS(SIGN(INT(d)))))
|| STRIP(CHAR(ABS(d)),L,'0') strip_c
FROM
( SELECT DEC(d,18,3) AS d
FROM
( VALUES 12.25, 100406.009, 1., 0.999, 0.001
, 0, -0.001, -0.999, -1., -100406.009) AS test_data(d)
)
;
------------------------------------------------------------------------------
D NITYA_ORIGINAL NITYA_MODIFIED STRIP_A STRIP_B STRIP_C
-------------------- --------------- ---------------- ---------------------- ---------------------- -----------------------
12.250 12.25 12.250 12.250 12.250 12.250
100406.009 100406.00 100406.009 100406.009 100406.009 100406.009
1.000 1.00 1.000 1.000 1.000 1.000
0.999 0.99 0.999 0.999 0.999 0.999
0.001 0.00 0.001 0.001 0.001 0.001
0.000 0.00 0.000 0.000 0.000 0.000
-0.001 0.00 0.001 -0.001 -0.001 -0.001
-0.999 0..99 0.999 -0.999 -0.999 -0.999
-1.000 -1..00 -1.000 -1.000 -1.000 -1.000
-100406.009 -100406..00 -100406.009 -100406.009 -100406.009 -100406.009
10 record(s) selected.
|
Last edited by tonkuma; 04-07-09 at 05:50.
|
04-06-09, 03:02
|
|
Registered User
|
|
Join Date: Mar 2009
Posts: 5
|
|
Hi,
Nitya's code will work for you.
if you don't know the precision in advance then try this code.
SELECT rtrim (char (integer (FEILD_NAME))) || '.'
|| substr (char (FEILD_NAME),
locate ('.', char (FEILD_NAME))+1,
length (char (FEILD_NAME)) - locate ('.', char (FEILD_NAME))
)
FROM TABLE_NAME
|
|
04-06-09, 03:37
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 1,829
|
|
|
|
ratheeshnellikkal, No.
I wrote:
Quote:
|
I tried to fix the issue(nitya_modified). But, I couldn't do it(minus sign is not displayed for 0 > field_value > -1).
|
Because, there is no minus zero(-0) in DB2. Zero is implicitly assumed plus.
Here is an example:
Code:
------------------------------ Commands Entered ------------------------------
SELECT FEILD_NAME
, rtrim (char (integer (FEILD_NAME))) || '.'
|| substr (char (FEILD_NAME),
locate ('.', char (FEILD_NAME))+1,
length (char (FEILD_NAME)) - locate ('.', char (FEILD_NAME))
) AS ratheeshnellikkal
FROM
( SELECT DEC(d,18,3) AS FEILD_NAME
FROM
( VALUES 12.25, 1., 0.999
, 0, -0.001, -0.999, -1., -100406.009) AS test_data(d)
) TABLE_NAME
;
------------------------------------------------------------------------------
FEILD_NAME RATHEESHNELLIKKAL
-------------------- --------------------------------
12.250 12.250
1.000 1.000
0.999 0.999
0.000 0.000
-0.001 0.001
-0.999 0.999
-1.000 -1.000
-100406.009 -100406.009
7 record(s) selected.
|
|
05-11-09, 06:39
|
|
Registered User
|
|
Join Date: Apr 2004
Posts: 54
|
|
replace(replace(rtrim(ltrim(replace(char(<field name>), '0', ' '))), ' ', '0'), '.', '')
is perfect for me, but my stored procedure is ko with:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0789N The data type for parameter or variable "UPDATECPV" is not supported
in the SQL routine. LINE NUMBER=8. SQLSTATE=429BB
My <field name> is a positive number bigger than integer, do you have any idea?
best regards.
|
|
08-07-09, 18:09
|
|
Registered User
|
|
Join Date: Aug 2009
Posts: 1
|
|
|
concat the integer and the fractional portion of the value
The best solution I figured out on how to remove leading zeros when converting Decimal to Char/Varchar in SQL is to concat the integer and the fractional portion of the value as shown in the following (standalone) example:
select concat(concat(rtrim(char(int(val))), '.'),
rtrim(right(char(val - int(val)), 3)))
from (values (10.20), (11), (.02), (.3), (123)) as mytab(val);
1
------
10.20
11.00
0.02
0.30
123.00
5 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
cheers
javaguru
|
|
08-08-09, 22:08
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 150
|
|
If you want to save a possible sign try to use following SQL:
Quote:
|
Strip ( substr(strip(dec_str), 1, 1) || strip( substr( strip(dec_str), 2), L, '0'), L, '0')
|
K.S.
|
|
08-08-09, 23:17
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 1,829
|
|
Quote:
The best solution I figured out on how to remove leading zeros when converting Decimal to Char/Varchar in SQL is to concat the integer and the fractional portion of the value as shown in the following (standalone) example:
select concat(concat(rtrim(char(int(val))), '.'),
rtrim(right(char(val - int(val)), 3)))
from (values (10.20), (11), (.02), (.3), (123)) as mytab(val);
1
------
10.20
11.00
0.02
0.30
123.00
5 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
|
Although it would be good for positive values,
it would fail for negative values(with non-zero fraction and/or 0 > val > -1).
I already pointed out the issue for 0 > field_value > -1.
Quote:
I wrote:
Quote:
|
I tried to fix the issue(nitya_modified). But, I couldn't do it(minus sign is not displayed for 0 > field_value > -1).
|
|
Here is an example:
Code:
------------------------------ Commands Entered ------------------------------
SELECT val
, concat(concat(rtrim(char(int(val))), '.'),
rtrim(right(char(val - int(val)), 3)))
FROM (VALUES (10.20), (11), (.02), (.3), (123)
, (-10.20), (-11), (-.02), (-.3), (-123)
) as mytab(val)
;
------------------------------------------------------------------------------
VAL 2
--------------- ----------------------------
10.20 10.20
11.00 11.00
0.02 0.02
0.30 0.30
123.00 123.00
-10.20 -10..20
-11.00 -11.00
-0.02 0..02
-0.30 0..30
-123.00 -123.00
10 record(s) selected.
By the way, DB2 9.7 for LUW supports VARCHAR_FORMAT(or TO_CHAR). For example:
Code:
------------------------------ Commands Entered ------------------------------
SELECT val
, VARCHAR_FORMAT(val)
FROM (VALUES (10.20), (11), (.02), (.3), (123)
, (-10.20), (-11), (-.02), (-.3), (-123)
) as mytab(val)
;
------------------------------------------------------------------------------
VAL 2
--------------- ------------------------------------------
10.20 10.20
11.00 11.00
0.02 0.02
0.30 0.30
123.00 123.00
-10.20 -10.20
-11.00 -11.00
-0.02 -0.02
-0.30 -0.30
-123.00 -123.00
10 record(s) selected.
|
|
08-10-09, 04:38
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,578
|
|
Yet another simple alternative is this:
Code:
TRIM(LEADING '0' FROM CHAR(<value>))
You avoid all the messy casting and string operations.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
|
|
08-10-09, 09:48
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 1,829
|
|
Simple TRIM is not enough for the values less than 1.
Code:
------------------------------ Commands Entered ------------------------------
SELECT val
, TRIM(LEADING '0' FROM CHAR(val)) AS trim
, TO_CHAR(val) AS to_char
FROM (SELECT DEC(d,18,3) AS val
FROM
( VALUES 12.25, 100056.009, 1., 0.123, 0.001
, 0, -0.001, -1., -12.25, -100056.009
) AS test_data(d)
)
;
------------------------------------------------------------------------------
VAL TRIM TO_CHAR
-------------------- -------------------- ------------------------------------------
12.250 12.250 12.250
100056.009 100056.009 100056.009
1.000 1.000 1.000
0.123 .123 0.123
0.001 .001 0.001
0.000 .000 0.000
-0.001 -000000000000000.001 -0.001
-1.000 -000000000000001.000 -1.000
-12.250 -000000000000012.250 -12.250
-100056.009 -000000000100056.009 -100056.009
10 record(s) selected.
|
|
08-10-09, 10:03
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 1,829
|
|
Here is another expression.
Code:
------------------------------ Commands Entered ------------------------------
SELECT val
, TO_CHAR(val) AS to_char
, LEFT(CHAR(val),1-(SIGN(val)+SIGN(ABS(val)-1))/2)
||STRIP(CHAR(ABS(val)),L,'0') AS expression
FROM (SELECT DEC(d,18,3) AS val
FROM
( VALUES 12.25, 100056.009, 10., 1., 0.123, 0.001
, 0, -0.001, -1., -10., -12.25, -100056.009
) AS test_data(d)
)
;
------------------------------------------------------------------------------
VAL TO_CHAR EXPRESSION
-------------------- ------------------------------------------ ----------------------------------------
12.250 12.250 12.250
100056.009 100056.009 100056.009
10.000 10.000 10.000
1.000 1.000 1.000
0.123 0.123 0.123
0.001 0.001 0.001
0.000 0.000 0.000
-0.001 -0.001 -0.001
-1.000 -1.000 -1.000
-10.000 -10.000 -10.000
-12.250 -12.250 -12.250
-100056.009 -100056.009 -100056.009
12 record(s) selected.
|
|
08-10-09, 11:58
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 816
|
|
Quote:
|
Originally Posted by DB2Plus
If you want to save a possible sign try to use following SQL:
Quote:
|
select Strip ( substr(strip(dec_str), 1, 1) || strip( substr( strip(dec_str), 2), L, '0'), L, '0')
|
K.S.
|
It's working very good on:
Quote:
select Strip ( substr(strip(dec_str), 1, 1) || strip( substr( strip(dec_str), 2), L, '0'), L, '0')
from (select varchar(-001234.5) dec_str from sysibm.sysdummy1) dm
|
But did not work properly on:
Quote:
select Strip ( substr(strip(dec_str), 1, 1) || strip( substr( strip(dec_str), 2), L, '0'), L, '0')
from (select varchar(0) dec_str from sysibm.sysdummy1) dm
|
Lenny
|
|
08-10-09, 13:44
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 816
|
|
This one is working without any questions:
Quote:
select
case when dec_str = 0 then '0'
else Strip ( substr(strip(varchar(dec_str)), 1, 1) || strip( substr( strip(varchar(dec_str)), 2), L, '0'), L, '0')
end "Decimal Formatted Number"
from (select -001.267 dec_str from sysibm.sysdummy1) dm
|
You can use it if you need it.
Lenny.
|
|
08-10-09, 15:58
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 1,829
|
|
Lenny, it will not work well for the values ABS(dec_str) < 1.
For example:
Code:
------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------
Database Connection Information
Database server = DB2/NT 9.7.0
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
A JDBC connection to the target has succeeded.
------------------------------ Commands Entered ------------------------------
SELECT dec_str
, CASE WHEN dec_str = 0 THEN '0'
ELSE Strip ( substr(strip(varchar(dec_str)), 1, 1) || strip( substr( strip(varchar(dec_str)), 2), L, '0'), L, '0')
END "Decimal Formatted Number"
FROM (VALUES -001.267, 0, 1234.5, 0.123, -0.123) dm(dec_str)
;
------------------------------------------------------------------------------
DEC_STR Decimal Formatted Number
---------------- ------------------------
-1.267 -1.267
0.000 0
1234.500 1234.500
0.123 .123
-0.123 -.123
5 record(s) selected.
|
|
08-10-09, 16:29
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 816
|
|
Thank you, Tohkuma, it works, but not nice !
Making the small change, and have almost the perfect statement:
Quote:
select
case when dec_str = 0 then '0'
when abs(dec_str) < 1 then
substr(strip(varchar(dec_str)), 1, 1) || substr( strip(varchar(dec_str)), 2)
else
Strip ( substr(strip(varchar(dec_str)), 1, 1) || strip( substr( strip(varchar(dec_str)), 2), L, '0'), L, '0')
end "Decimal Formatted Number"
from (select -0.987654321 dec_str from sysibm.sysdummy1) dm
|
Lenny. 
|
|
08-11-09, 04:47
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,053
|
|
Quote:
|
Originally Posted by stolze
Yet another simple alternative is this:
Code:
TRIM(LEADING '0' FROM CHAR(<value>))
You avoid all the messy casting and string operations. |
Indeed, at least, for strictly positive integers.
For the number 0, it's giving an unwanted empty string...
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
|
|
| Thread Tools |
|
|
| 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
>
db2-- Decimal to Varchar without leading zeros
