On 7 23 , 6 07 , sybrandb <sybra..._at_gmail.com> wrote:
> On Jul 23, 11:49 am, haomiao <miaoh..._at_ustc.edu> wrote:
>
>
>
> > On 7 23 , 5 16 , sybrandb <sybra..._at_gmail.com> wrote:
>
> > > On Jul 23, 10:49 am, haomiao <miaoh..._at_ustc.edu> wrote:
>
> > > > Hi
>
> > > > (1) When I run
> > > > select 1 from dual where '1 ' = substr('21',2,1)
> > > > no result return. There is spaces in the left operand.
>
> > > > (2) When I run
> > > > select 1 from dual where '1 ' = substr('21',
> > > > 2,1) || ' '
> > > > 1 result return
>
> > > > (3) And when I run
> > > > select 1 from dual where '1 ' = '1'
> > > > 1 result return
>
> > > > What is the difference between (1)and(3)?
> > > > Can I find a better method than (2) , so I can eaily compare
> > > > without counting the spaces in '1 '
>
> > > > Thanks.
> > > >From the docs
>
> > > Blank-Padded Comparison Semantics
> > > If the two values have different lengths, then Oracle first adds
> > > blanks to the end of the shorter one so their lengths are equal.
> > > Oracle then compares the values character by character up to the first
> > > character that differs. The value with the greater character in the
> > > first differing position is considered greater. If two values have no
> > > differing characters, then they are considered equal. This rule means
> > > that two values are equal if they differ only in the number of
> > > trailing blanks. Oracle uses blank-padded comparison semantics only
> > > when both values in the comparison are either expressions of datatype
> > > CHAR, NCHAR, text literals, or values returned by the USER function.
>
> > > Your example 3 is made up by text literals, consequently the right
> > > hand side of the expression will be padded.
> > > Your example 1 will not be padded
>
> > What is the datatype of substr('21',2,1)? text literals?
> > What is the datatype of text literals?
>
> > How can I convert substr('21',2,1) to CHAR?
>
> You actually did read the quote from the docs?
> Doesn't look like.
> Question 2 is irrelevant.
> Repeating part of the quote...
> Oracle uses blank-padded comparison semantics only
> when both values in the comparison are either expressions of datatype
> CHAR, NCHAR, *text literals*, or values returned by the USER function
> Text literals do not have a datatype.
> A substr returns a varchar2. Hence is your original statement (1)
> variable length semantics are being used.
> If you really want to convert a varchar2 to char as opposed to the
> much more elegant solution of LTRIMming the left hand side expression,
> to get rid of the redundant spaces, look up the CAST function in your
> documentation.
>
> --
> Sybrand Bakker
> Senior Oracle DBA- -
>
> - -
I undertand, thank you.
Received on Mon Jul 23 2007 - 06:04:46 CDT
