How String Comparisons Differ in Oracle
String comparisons between VARCHAR (VARCHAR2) and CHAR data types in Oracle behave differently from those in Ingres, Microsoft SQL Server, and DB2 UDB.
In Oracle, the length of the data type is a significant factor in the comparison. If the same string is compared, (for example, “foobar”), but the data type lengths are different (for example, VARCHAR(6) and CHAR(8)), Oracle states that the strings are unequal.
The following script best illustrates this point. The script behaves the same when run against Ingres, Microsoft SQL Server and DB2 UDB, and returns the same result for both SELECT statements. In Oracle, however, the script only returns data for the first SELECT statement.
Note: The script can be run in either SQL*Plus or via the gateway.
/*
** col_vc type could be either varchar OR varchar2.
** NOTE also applies to Unicode types like nvarchar too.
*/
create table test_cmp
(
col_vc varchar(6),
col_c char(8)
);
insert into test_cmp values('foobar', 'foobar');
commit;
select col_vc, col_c
from test_cmp
order by 1;
/* returns no rows in Oracle */
select col_vc, col_c
from test_cmp
where col_vc = col_c
order by 1;