RE: sql query substr
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <jkstill@xxxxxxxxx>, <jsilverman@xxxxxxxxxxxxx>
- Date: Thu, 9 Feb 2006 06:55:13 -0500
And, I'm fairly certain it was in 8.0, and I wouldn't be surprised if it
were in 7.3 and earlier as well...;-)
--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning
"There are 10 types of people in the world: Those who understand
binary, and those who don't."
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
Sent: Wednesday, February 08, 2006 8:11 PM
To: jsilverman@xxxxxxxxxxxxx
Cc: Krishan.Gupta@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: sql query substr
Too late, there it is, in the 'Format models' section of the 8i docs.
<sigh>
On 2/8/06, Jared Still < jkstill@xxxxxxxxx <mailto:jkstill@xxxxxxxxx> >
wrote:
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
<mailto: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
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
BEGIN:VCARD
VERSION:2.1
N:Bobak;Mark
FN:Bobak, Mark
ORG:ProQuest Information and Learning;Database Group
TITLE:Software Architect, Sr-TPD
TEL;WORK;VOICE:734.975.6086
ADR;WORK:;ECC4-N01E04;1400 Eisenhower Parkway
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:ECC4-N01E04=0D=0A1400 Eisenhower Parkway
EMAIL;PREF;INTERNET:Mark.Bobak@xxxxxxxxxxxxxxx
REV:20051213T223654Z
END:VCARD
Other related posts:
