RE: sql query substr

From: Jim Silverman <jsilverman_at_Solucient.com>
Date: Thu, 9 Feb 2006 08:02:36 -0500
Message-ID: <2D1EFB433A527D4681B80C94B71E359804123227@aaexchange01.HEALTH.AD>

It's been around for at least that long. I started with Oracle 6.0, and am pretty sure it was there then, but I wouldn't swear to it. However, I know it's been here since at least 7.1. The "tm" element, however, was new to me; I hadn't seen it until I glanced over the 10g documentation yesterday. (This isn't to say that it is new with 10g, but this is just the first opportunity I've had to run across it.)

Glad it was a productive day for both of us!

---

Jim Silverman
Senior Systems Database Administrator
Solucient, LLC
Telephone: 734-669-7641

FAX:            734-930-7611 
E-Mail:         jsilverman_at_solucient.com 

		From: Jared Still [mailto:jkstill_at_gmail.com] 
		Sent: Wednesday, February 08, 2006 8:08 PM
		To: Jim Silverman
		Cc: Krishan.Gupta_at_etcc.com; oracle-l_at_freelists.org
		Subject: 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] 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

Received on Thu Feb 09 2006 - 07:02:36 CST