Re: sql query substr

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> 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<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

--
http://www.freelists.org/webpage/oracle-l