Database Reference
In-Depth Information
EODA@ORA12CR1> select * from t where char_column = 'Hello World';
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
EODA@ORA12CR1> select * from t where varchar2_column = 'Hello World';
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
So far, the columns look identical but, in fact, some implicit conversion has taken place and the
CHAR(11)
literal
'Hello World' has been promoted to a
CHAR(20)
and blank padded when compared to the
CHAR
column. This must
have happened since
Hello World.........
is
not the same
as
Hello World
without the trailing spaces. We can
confirm that these two strings are materially different:
EODA@ORA12CR1> select * from t where char_column = varchar2_column;
no rows selected
They are not equal to each other. We would have to either blank pad out the
VARCHAR2_COLUMN
to be 20 bytes in
length or trim the trailing blanks from the
CHAR_COLUMN
, as follows:
EODA@ORA12CR1> select * from t where trim(char_column) = varchar2_column;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
EODA@ORA12CR1> select * from t where char_column = rpad( varchar2_column, 20 );
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
■
Note
there are many ways to blank pad the
VARCHAR2_COLUMN
, such as using the
CAST()
function.
The problem arises with applications that use variable length strings when they bind inputs, with the resulting
“no data found” that is sure to follow:
EODA@ORA12CR1> variable varchar2_bv varchar2(20)
EODA@ORA12CR1> exec :varchar2_bv := 'Hello World';
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select * from t where char_column = :varchar2_bv;
no rows selected
EODA@ORA12CR1> select * from t where varchar2_column = :varchar2_bv;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
