The following techniques, while more compact than the solutions presented in SQL + PL/SQL , unfortunately only work in SQL commands, not PL/SQL commands.
DECODE
One approach uses the DECODE function. Unlike the "=" operator, DECODE treats two nulls as equivalent. To return rows where two columns contain the same value we can therefore use a command like the following.
select * from t where DECODE( C2, C3, 'Y', 'N' ) = 'Y' ;
C1 C2 C3 --- ---------- ---------- 1 A A 4 (null) (null)
DUMP
Another approach uses the DUMP function.
select * from t where DUMP(C2) = DUMP(C3) ;
C1 C2 C3 --- ---------- ---------- 1 A A 4 (null) (null)
This approach has a couple of limitations however. One, the output of the DUMP function is truncated at 4000 characters. If the values being compared produce truncated DUMP output then the comparison can produce false positives. Here is an example.
select 'Oops! This row should not be returned.' as result from dual where DUMP( LPAD( 'A', 4000) ) = DUMP( LPAD( 'B', 4000 ) ) ;
RESULT -------------------------------------- Oops! This row should not be returned.
Two, C2 and C3 must be the exact same datatype for the comparison to work. Comparing compatible datatypes such as VARCHAR2 and CHAR will fail to match any rows.
select 'Oops! This row should be returned, but it is not.' as result from t where dump( c2 ) = dump( 'A' ) ; no rows selected
Examining the output of DUMP shows why this occurs. The "typ=" part of the DUMP output for both terms differs because column C2 is datatype 1, VARCHAR2, and the literal 'A' is datatype 96, CHAR.
column varchar2_val format a30 column char_val format a30 fold_before select dump( c2 ) as varchar2_val , dump( 'A' ) as char_val from t where c1 = 1 ;
VARCHAR2_VAL ------------------------------ CHAR_VAL ------------------------------ Typ=1 Len=1: 65 Typ=96 Len=1: 65
SYS_OP_MAP_NONNULL
Another approach that some have proposed uses the undocumented function SYS_OP_MAP_NONNULL.
select * from t where SYS_OP_MAP_NONNULL( C2 ) = SYS_OP_MAP_NONNULL( C3 ) ;
C1 C2 C3 --- ---------- ---------- 1 A A 4 (null) (null)
As with the other solutions on this page, SYS_OP_MAP_NONNULL does not work in PL/SQL.
begin for r in ( select * from t ) loop if ( SYS_OP_MAP_NONNULL( R.C2 ) = SYS_OP_MAP_NONNULL( R.C3 ) ) then dbms_output.put_line( 'Row ' || r.c1 || ' contains matching values.' ); end if; end loop; end; . /
if ( SYS_OP_MAP_NONNULL( R.C2 ) = SYS_OP_MAP_NONNULL( R.C3 ) ) * ERROR at line 6: ORA-06550: line 6, column 10: PLS-00201: identifier 'SYS_OP_MAP_NONNULL' must be declared ORA-06550: line 6, column 5: PL/SQL: Statement ignored
It also has a length limitation.
select * from dual where SYS_OP_MAP_NONNULL( LPAD( 'A', 4000 ) ) = SYS_OP_MAP_NONNULL( LPAD( 'B', 4000 ) ) ; select * * ERROR at line 1: ORA-01706: user function result value was too large
While undocumented features such as this one are compelling, their behaviour or availability can change at any time making them a risky thing to include in your code. They also make support and maintenance harder for others who need to work with your code and are not familiar with the feature.