| |
|
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.
|
 |
05-21-09, 16:30
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
|
How to convert decimal(10,2) to STRING.
|
|
Hi friends,
Another things that was brought into my notice as below.
Code:
D:\TD>db2 select cast(real_chips as decimal(10,2)) from client_acc where acc_num = 'TT0529656034'
REAL_CHIPS
------------
105.00
1 record(s) selected.
D:\TD>db2 select char(cast(real_chips as decimal(10,2))) from client_acc where acc_num = 'TT0529656034'
1
------------
00000105.00
1 record(s) selected.
D:\TD>db2 select cast(real_chips as decimal(10,2)) from client_acc where acc_num = 'TT0529656034'
REAL_CHIPS
------------
105.00
1 record(s) selected.
D:\TD>db2 select real_chips from client_acc where acc_num = 'TT0529656034'
REAL_CHIPS
------------
105.00
1 record(s) selected.
D:\TD>db2 select cast(real_chips as char) from client_acc where acc_num = 'TT0529656034'
1
-
0
SQL0445W Value "00000105.00 " has been truncated. SQLSTATE=01004
1 record(s) selected with 1 warning messages printed.
D:\TD>db2 select cast(real_chips as char(10)) from client_acc where acc_num = 'TT0529656034'
1
----------
00000105.0
SQL0445W Value "00000105.00 " has been truncated. SQLSTATE=01004
1 record(s) selected with 1 warning messages printed.
D:\TD>db2 select real_chips from client_acc where acc_num = 'TT0529656034'
REAL_CHIPS
------------
105.00
1 record(s) selected.
D:\TD>db2 select ltrim(cast(real_chips as char)) from client_acc where acc_num = 'TT0529656034'
1
-
0
SQL0445W Value "00000105.00 " has been truncated. SQLSTATE=01004
1 record(s) selected with 1 warning messages printed.
The requirement was to convert REAL_CHIPS to character string form decimal(10,2).
Any help ??
Thanks
DBFinder
|
|
05-21-09, 17:27
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
DBFinder, Decimal(10,2) converted to character would need 10 characters for the Numbers, 1 for the decimal point and (if negative numbers) 1 for the sign.
To be safe you should use:
cast(real_chips as char(12))
Note: one clue to the number of characters you need is the number of '-' between the column name and the value when you cast it as Decimal(10,2). In you case it is '------------' which is 12 dashes.
|
|
05-21-09, 18:08
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
|
|
Code:
C:\Program Files\IBM\SQLLIB\BIN>db2 select cast(real_chips as char(12)) from client_acc where acc_num='TT0529656034'
1
------------
00000105.00
1 record(s) selected.
So, Stealth_DBA , what is the difference.
The point was to convert to character string. without left padding.
Is there any DB2 function to get following result
The developer wants to use a scalar function in the query.
DBFinder
|
|
05-21-09, 18:24
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
It depends on which DB2 version you have. In DB2 9.5 there is a STRIP function.
STRIP(string, L/T/B, strip-character)
String is the item to be Stripped.
L/T/B is on of Leading, Trailing or Both
strip-character by default is space but you can specify other character(s)
Assuming you don't have any negative numbers:
STRIP( CAST( real_chips as CHAR(10) ), L, '0' )
NOTE: that '0' is a zero.
But it should really be:
STRIP( CAST( real_chips as CHAR(11) ), L, '0' )
since you could (technically) have 99999999.99 as a value (assuming no negatives) and that is 11 characters and truncation would occur.
|
|
05-21-09, 18:39
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
Quote:
C:\Program Files\IBM\SQLLIB\BIN>DB2 VALUES STRIP('00056',L,'0')
SQL0206N "L" is not valid in the context where it is used. SQLSTATE=42703
C:\Program Files\IBM\SQLLIB\BIN>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09050" with
level identifier "03010107".
Informational tokens are "DB2 v9.5.0.808", "s071001", "NT3295", and Fix Pack
"0".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".
|
I agree, but what is that = STRIP does not work ??
DBFinder
|
|
05-21-09, 18:52
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
DBFinder, try this in either Command Editor or Command Window:
SELECT '000056', STRIP('000056',L,'0') FROM SYSIBM.SYSDUMMY1
In a Command Window I ran:
Quote:
E:\Program Files\IBM\SQLLIB\BIN>DB2 SELECT '000056', STRIP('000056',L,'0') FROM SYSIBM.SYSDUMMY1
1 2
------ ------
000056 56
|
My DB2LEVEL looks the same as yours:
Quote:
E:\Program Files\IBM\SQLLIB\BIN>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09050" with
level identifier "03010107".
Informational tokens are "DB2 v9.5.0.808", "s071001", "NT3295", and Fix Pack
"0".
Product is installed at "E:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".
|
|
|
05-21-09, 19:01
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
Code:
C:\Program Files\IBM\SQLLIB\BIN>DB2 SELECT '000056', STRIP('000056',L,'0') FROM SYSIBM.SYSDUMMY1
1 2
------ ------
000056 56
1 record(s) selected.
That's what I did already !
I was connecting to the database on Ver 8.2 server.
So Strip is only for V 9.5
|
|
05-21-09, 19:14
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
That is correct. STRIP is NOT in V8.2.
Getting rid of leading zeroes without STRIP might still be possible but it won't be 'pretty'. Let me think about it (unless someone else has an idea).
|
|
05-21-09, 19:47
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
This should work (assuming no negative numbers).
REAL_CHIPS is CAST to a CHAR(11) and as SUBSTR is used with the value of REAL_CHIPS used to determine the Start and Length parameters.
Code:
SELECT SUBSTR(CAST(real_chips AS CHAR(11)),
CASE
WHEN real_chips <= .99 THEN 9
WHEN real_chips <= 9.99 THEN 8
WHEN real_chips <= 99.99 THEN 7
WHEN real_chips <= 999.99 THEN 6
WHEN real_chips <= 9999.99 THEN 5
WHEN real_chips <= 99999.99 THEN 4
WHEN real_chips <= 999999.99 THEN 3
WHEN real_chips <= 9999999.99 THEN 2
ELSE 1
END,
CASE
WHEN real_chips <= .99 THEN 3
WHEN real_chips <= 9.99 THEN 4
WHEN real_chips <= 99.99 THEN 5
WHEN real_chips <= 999.99 THEN 6
WHEN real_chips <= 9999.99 THEN 7
WHEN real_chips <= 99999.99 THEN 8
WHEN real_chips <= 999999.99 THEN 9
WHEN real_chips <= 9999999.99 THEN 10
ELSE 11
END)
FROM SYSIBM.SYSDUMMY1
;
It does NOT have a leading 0 for anything less than 1 (ex. .99) but that shouldn't be a problem.
|
|
05-22-09, 03:11
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Assumed that data type of real_chip is DECIMAL(10,2) and values of real_chip are not negative.
REPLACE(LTRIM(REPLACE(CHAR(real_chip, 12), '0', ' ')), ' ', '0')
or
TRANSLATE(LTRIM(TRANSLATE(CHAR(real_chip, 12), ' ', '0')), '0', ' ')
where CHAR(real_chip, 12) can be replaced by CAST(real_chip AS CHAR(12)).
|
|
05-22-09, 10:56
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
Thanks tonkuma ,
Actually I added RTRIM to strip a space on Right before translate.
Good work. Thanks again
Code:
TRANSLATE(LTRIM(TRANSLATE(RTRIM(CHAR(REAL_CHIPS)), ' ', '0')), '0', ' ')
DBFinder
|
|
| 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(10,2) to STRING.
