Re: question about substr

> haomiao 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.
>

On Jul 23, 9:58 am, DA Morgan <damor..._at_psoug.org> wrote:

> Consider using TRIM
> http://www.psoug.org/reference/string_func.html#strm

TRIM certainly warranted; don't forget about the "null: case....

TRIM() on a string of all blanks will give you NULL, which will not compare compare "true" against anything, including NULL from a TRIM() of what you think should be "equal." Thus:

    select 'x' from dual
    /* Compare three-spaces vs. five-spaces */     where ' ' = ' ';

compares "true" and yields one row, while

    select 'x' from dual
    /* Compare TRIM(three-spaces) vs. TRIM(five-spaces) */     where TRIM(' ') = TRIM(' ');

compares "false" and yields no rows.

In this particular case (strings w/ all blanks) the behavior you get w/ TRIM() is different than that of Oracle's rules for comparing blank-padded literals.

There are well-known workarounds for equality comparison on strings A and B, handling NULLs as "equal":

   ( A = B OR ( A IS NULL AND B IS NULL ) )   NVL(A, 'ImpossibleValue') = NVL(B, 'ImpossibleValue')   sys_op_map_nonnull(A) = sys_op_map_nonnull(B)   A || 'X' = B || 'X"

here's an old thread about it

http://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/dc20fcd9e6dc8cc3/68aa2de100b00342?hl=en&lr=&ie=UTF-8&oe=UTF-8&rnum=1&prev=/groups%3Fq%3Dsys_op_map_nonnull%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3Dbpsipq%2524sas%25241%25248302bc10%2540news.demon.co.uk%26rnum%3D1#68aa2de100b00342

Hope that helps,
JH Received on Mon Jul 23 2007 - 11:55:55 CDT