Re: question about substr

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?

>
> --
> Sybrand Bakker
> Senior Oracle DBA- -
>
> - -
Received on Mon Jul 23 2007 - 04:49:02 CDT