Oracle varchar2 and “nonpadded comparison semantics”.

The Oracle varchar2 data type has “nonpadded comparison semantics” as per

http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1822

This means values with trailing whitespace can be stored e.g. Under version 12.1.0.2.0:

SQL> create table a ( b varchar2 (30));

Table created.

SQL> insert into a values (‘abc ‘);

1 row created.

SQL> select ‘x’||b||’x’ from a;

‘X’||B||’X’
——————————–
xabc x

but not directly compared against a value with trailing whitespace

SQL> select * from a where b=’123 ‘;

no rows selected

We can compare against a value with trailing whitespace by first casting to the CHAR datatype which has “blank-padded comparison semantics”:

select * from a where cast (b as char(30)) = ‘abc ‘;

B
——————————
abc

Using a trigger to fix the issue

SQL> create user c##dave identified by x;

User created.

SQL> GRANT CONNECT,RESOURCE,DBA TO c##dave;

Grant succeeded.

SQL> GRANT CREATE SESSION TO c##dave;

Grant succeeded.

SQL> alter session set current_schema = c##dave;

Session altered.

SQL> create table a ( b varchar2 (30));

Table created.

SQL> CREATE OR REPLACE TRIGGER a_insert_update
BEFORE INSERT OR UPDATE ON a
FOR EACH ROW
DECLARE
my_varchar2 VARCHAR2(30);
BEGIN
— Force to char to remove trailing whitespace
my_varchar2 := :NEW.b;
:NEW.b := RTRIM(my_varchar2);
END;
/  2    3    4    5    6    7    8    9   10   11

Trigger created.

SQL> insert into a values (‘abc ‘);

1 row created.

SQL> select ‘x’||b||’x’ from a;

‘X’||B||’X’
——————————–
xabcx

SQL> drop table a;

Table dropped.

SQL> alter session set current_schema = sys;

Session altered.

SQL> drop user c##dave;

User dropped.