RE: sql query substr

From: Jim Silverman <jsilverman_at_Solucient.com>
Date: Wed, 8 Feb 2006 16:52:08 -0500
Message-ID: <2D1EFB433A527D4681B80C94B71E3598041231C2@aaexchange01.HEALTH.AD>

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)

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

Received on Wed Feb 08 2006 - 15:52:08 CST