Re: sql query substr
- From: Jared Still <jkstill@xxxxxxxxx>
- To: jsilverman@xxxxxxxxxxxxx
- Date: Wed, 8 Feb 2006 17:07:36 -0800
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@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx<oracle-l-bounce@xxxxxxxxxxxxx>
> ]*** On Behalf Of* Krishan Gupta
> ***Sent:* Wednesday, February 08, 2006 4:32 PM
> ***To:* oracle-l@xxxxxxxxxxxxx
> ***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
Other related posts:
