1 2 3 Previous Next 43 Replies Latest reply: Dec 7, 2007 8:48 AM by 494018 Go to original post RSS
      • 30. Re: Using <> or != on a NULL
        Laurent Schneider
        is not it
        ^=
        instead of
        ~=
        ?
        • 31. Re: Using <> or != on a NULL
          MichaelS
          >> I don't find the ~= operator in documentation, however it can be compiled and seems to > be equivalent to not equal ( != ) operator, just curious, can you elaborate?

          and

          > is not it
          ^=
          instead of
          ~=
          ?

          Relational Operators or Operator Precedence

          Added another Link.

          Message was edited by:
          michaels
          • 32. Re: Using <> or != on a NULL
            MaximDemenko
            Thanks for the link, Michael

            Best regards

            Maxim
            • 33. Re: Using <> or != on a NULL
              damorgan
              Thanks for the correction ... there are four ... not three.
              set serveroutput on

              DECLARE
              i POSITIVE := 1;
              BEGIN
                IF i <> 0 THEN
                  dbms_output.put_line('A');
                END IF;

                IF i != 0 THEN
                  dbms_output.put_line('B');
                END IF;

                IF i ^= 0 THEN
                  dbms_output.put_line('C');
                END IF;

                IF i ~= 0 THEN
                  dbms_output.put_line('D');
                END IF;
              END;
              /
              • 34. Re: Using <> or != on a NULL
                Laurent Schneider
                ~= rules! as NVL(BOOLEAN,BOOLEAN) it is a pure PLSQL solution ;-)
                • 35. Re: Using <> or != on a NULL
                  MichaelS
                  ~= rules! as NVL(BOOLEAN,BOOLEAN) it is a pure PLSQL solution ;-)
                  Don't you mean ^= ? ;)
                  SQL> select * from dual where 1 ~= 2
                                             *
                  Error at line 1
                  ORA-00911: invalid character
                  
                  SQL> select * from dual where 1 ^= 2
                  
                  D
                  -
                  X
                  
                  
                  1 row selected.
                  SQL> select * from dual where 1 != 2
                  
                  D
                  -
                  X
                  
                  
                  1 row selected.
                  SQL> select * from dual where 1 <> 2
                  
                  D
                  -
                  X
                  
                  
                  1 row selected.
                  • 36. Re: Using <> or != on a NULL
                    damorgan
                    ~= is only valid in PL/SQL.
                    • 37. Re: Using <> or != on a NULL
                      cd_2
                      As a finishing touch I made a summary of this thread (without the syntax discussion of ~=):
                      IF NULL or how to match two variables that can have NULL as value.

                      C.
                      • 38. Re: Using <> or != on a NULL
                        610329
                        IF x<>y OR (x IS NULL)<>(y IS NULL) THEN
                        dbms_output.put_line('Shorter and easier to read, too');
                        END IF;
                        • 39. Re: Using <> or != on a NULL
                          610329
                          "(x IS NULL)<>(y IS NULL)" only works in PL/SQL, though
                          • 40. Re: Using <> or != on a NULL
                            Aketi Jyuuzou
                            Oh below good solution is used "Boolean Type".
                            "Boolean Type" can use on PL/SQL.
                            SQL> select version from v$instance;
                            VERSION
                            -----------------
                            10.1.0.2.0
                            SQL> begin
                              2      for rec in (select 1 as X,1 as Y from dual
                              3             union all select 1,2 from dual
                              4             union all select 1,null from dual
                              5             union all select null,1 from dual
                              6             union all select null,null from dual) Loop
                              7          IF rec.x<>rec.y OR (rec.x IS NULL)<>(rec.y IS NULL) THEN
                              8              DBMS_Output.Put_Line(nvl(to_char(rec.X),'null') || '<>'
                              9                                || nvl(to_char(rec.Y),'null'));
                            10          else
                            11              DBMS_Output.Put_Line(nvl(to_char(rec.X),'null') || '=='
                            12                                || nvl(to_char(rec.Y),'null'));
                            13          end if;
                            14      end Loop;
                            15  end;
                            16  /
                            1==1
                            1<>2
                            1<>null
                            null<>1
                            null==null
                            I converted above solution which can use on SQL :-)
                            SQL> select X,Y,
                              2  case when X != Y or nvl2(X,0,1) != nvl2(Y,0,1) then '<>' else '==' end as test
                              3  from (select 1 as X,1 as Y from dual
                              4  union all select 1,2 from dual
                              5  union all select 1,null from dual
                              6  union all select null,1 from dual
                              7  union all select null,null from dual);
                               X     Y  TEST
                            ----  ----  ----
                               1     1  ==
                               1     2  <>
                               1  null  <>
                            null     1  <>
                            null  null  ==
                            I seem that these solutions are used XOR (http://mathworld.wolfram.com/XOR.html).
                            • 41. Re: Using <> or != on a NULL
                              494018
                              If col1 has a number, date, timestamp, small varchar2/char, etc. datatype then the following approach also works.
                              where dump( tableA.col1 ) <> dump( tableB.col1 )
                              It works similar to the SYS_OP_MAP_NONNUL approach proposed earlier, except that DUMP is a documented, supported function and SYS_OP_MAP_NONNULL is not.

                              Unfortunately this only works in SQL, not PL/SQL, and does not work with values that contain a lot of bytes. See SQL Snippets: Nulls and Equality - SQL Only for more details.

                              --
                              Joe Fuda
                              SQL Snippets
                              • 42. Re: Using <> or != on a NULL
                                Laurent Schneider
                                Even if it is fun, I would terribly avoid this, because it avoid comparing compatible types like CHAR and VARCHAR2
                                select 
                                  hiredate,dump(date '1980-04-19'),dump(hiredate),
                                  ename, dump('SCOTT'),dump(ENAME)
                                from emp
                                where ename='SCOTT';
                                HIREDATE   DUMP(DATE'1980-04-19')            DUMP(HIREDATE)                   
                                ---------- --------------------------------- ---------------------------------
                                ENAME      DUMP('SCOTT')                     DUMP(ENAME)                      
                                ---------- --------------------------------- ---------------------------------
                                19-APR-87  Typ=13 Len=8: 7,188,4,19,0,0,0,0  Typ=12 Len=7: 119,187,4,19,1,1,1 
                                SCOTT      Typ=96 Len=5: 83,67,79,84,84      Typ=1 Len=5: 83,67,79,84,84      
                                                                                                                  
                                • 43. Re: Using <> or != on a NULL
                                  494018
                                  Ah, good observation Laurent. Thanks for pointing that out. I've updated my SQL Snippets page with this new info. and credited you at the bottom.

                                  --
                                  Joe Fuda
                                  SQL Snippets
                                  1 2 3 Previous Next