Re: question about substr

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

--
Sybrand Bakker
Senior Oracle DBA