RE: sql query substr
- From: "Jim Silverman" <jsilverman@xxxxxxxxxxxxx>
- To: <Krishan.Gupta@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 8 Feb 2006 16:52:08 -0500
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] 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)
Other related posts:
