| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql query substr
Hello list,
I learned something valuable.
B. Polarski
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jim Silverman
Sent: donderdag 9 februari 2006 14:03
To: Jared Still
Cc: Krishan.Gupta_at_etcc.com; oracle-l_at_freelists.org
Subject: RE: sql query substr
It’s been around for at least that long. I started with Oracle 6.0, and am pretty sure it was there then, but I wouldn’t swear to it. However, I know it’s been here since at least 7.1. The “tm” element, however, was new to me; I hadn’t seen it until I glanced over the 10g documentation yesterday. (This isn’t to say that it is new with 10g, but this is just the first opportunity I’ve had to run across it.) Glad it was a productive day for both of us!
FAX: 734-930-7611
E-Mail: jsilverman_at_solucient.com
From: Jared Still [mailto:jkstill_at_gmail.com]
Sent: Wednesday, February 08, 2006 8:08 PM
To: Jim Silverman
Do you know what at which version the 'fill mode' was introduced?
Please don't say 8i, or 7.3.
I'd hate to think I had missed it for that long. :)
Jared
On 2/8/06, Jim Silverman <jsilverman_at_solucient.com> wrote:
Krishnan, this is how the to_char function works: it always leaves one extra leading space in the output to accommodate a potential minus sign when the format mask consists only of "9 "s (and potentially a decimal point alignment character). You can eliminate this by including the "fm " (fill-mode) element (i.e., "fm99.99 "), or use the "tm " (i.e., "text minimum" ) element. In the "tm " case, you can only follow this element with a single "9 ", i.e., "tm9 ", in your format mask.
HTH
Telephone: 734-669-7641 FAX: 734-930-7611 E-Mail: jsilverman_at_solucient.comFrom: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Krishan Gupta Sent: Wednesday, February 08, 2006 4:32 PM To: oracle-l_at_freelists.org
select substr(to_char(34.25,'99.99'),1,5) col1 from dual; --result is ' 34.2' select substr(to_char(-4.25,'99.99'),1,5) col1 from dual; --result is ' -4.2' select substr(to_char(34.25,'99.99'),2,5) col1 from dual; --result is '34.25'select substr(to_char(-4.25,'99.99'),2,5) col1 from dual; --result is '-4.25' TIA
This message is a private communication. It may contain information that is confidential
and legally protected from disclosure. If you are not an intended recipient, please do
not read, copy or use this message or any attachments, and do not disclose them to others.
Please notify the sender of the delivery error by replying to this message, and then
delete it and any attachments from your system.
Thank you,
Solucient LLC
(eXclaimer 4x)
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
 |
 |