Re: question about substr

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