RE: sql query substr

  Hello list,
  
  I learned something valuable. 
  
  B. Polarski
  
  -----Original Message-----
From:   oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On 
Behalf   Of Jim Silverman
Sent: donderdag 9 februari 2006   14:03
To: Jared Still
Cc: Krishan.Gupta@xxxxxxxx;   oracle-l@xxxxxxxxxxxxx
Subject: RE: sql query substr

  
  
  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@xxxxxxxxxxxxx 
      From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Wednesday, February 08, 2006 8:08 PM
To: Jim Silverman
Cc: Krishan.Gupta@xxxxxxxx;   oracle-l@xxxxxxxxxxxxx
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@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] 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

  
  
                
---------------------------------
 Nouveau : téléphonez moins cher avec Yahoo! Messenger ! Découvez les tarifs 
exceptionnels pour appeler la France et l'international.Téléchargez la version 
beta.

Other related posts: