Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql query substr
And, I'm fairly certain it was in 8.0, and I wouldn't be surprised if it
were in 7.3 and earlier as well...;-)
-- Mark J. Bobak Senior Oracle Architect ProQuest Information & Learning "There are 10 types of people in the world: Those who understand binary, and those who don't." ________________________________ From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Wednesday, February 08, 2006 8:11 PM To: jsilverman_at_solucient.com Cc: Krishan.Gupta_at_etcc.com; oracle-l_at_freelists.org Subject: Re: sql query substr Too late, there it is, in the 'Format models' section of the 8i docs.Received on Thu Feb 09 2006 - 05:55:13 CST
<sigh>
On 2/8/06, Jared Still < jkstill_at_gmail.com <mailto:jkstill_at_gmail.com> > wrote: Thanks Jim, I learned something today. :) 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
<mailto: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 ===================================== Jim Silverman Senior Systems Database Administrator Solucient, LLC Telephone: 734-669-7641 FAX: 734-930-7611 E-Mail: jsilverman_at_solucient.com From: 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 Subject: sql query substr Hello List, Look into the queries below, why the result of first two queries is starting with a blank space, and to get rid of this I have to substr from position 2 rather than 1. 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 Krishan 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 -- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist
![]() |
![]() |