CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Enoque Duarte
Enoque Duarte

Reputation: 689

SQLDeveloper query automatic padding CHAR field

Given the table ATABLE with a field AFIELD of type CHAR(8), and where i have a field with value "1234567 "

Why, in SQL Developer, if I query:

SELECT * FROM ATABLE WHERE AFIELD = '1234567';

It will automatically pad the missing space and return the results, and if I query with :

SELECT * FROM ATABLE WHERE AFIELD = :value;

and input the value, it wont ?

Upvotes: 0

Views: 683

Answers (2)

Alex Poole
Alex Poole

Reputation: 191255

From the documentation:

Within expressions and conditions, Oracle treats text literals as though they have the data type CHAR by comparing them using blank-padded comparison semantics.

When you do WHERE AFIELD = '1234567' the text literal '1234567' is treated as char and blank-padded comparison semantics are used to compare the column value and the literal. Even though the literal doesn't have the trailing space, those semantics see them as the same, so it finds a match.

When you use a bind variable the literal you assign to it is a char, but the bind variable itself is varchar2 - even if you declare it as char, oddly, though in that case the value is blank-padded anyway:

var char_value char(8);
exec :char_value := '1234567';
var varchar2_value varchar2(8);
exec :varchar2_value := '1234567';

select dump('1234567') as d1, dump(:char_value) as d2, dump(:varchar2_value) as d3
from dual;

D1                                   D2                                   D3                                  
------------------------------------ ------------------------------------ ------------------------------------
Typ=96 Len=7: 49,50,51,52,53,54,55   Typ=1 Len=8: 49,50,51,52,53,54,55,32 Typ=1 Len=7: 49,50,51,52,53,54,55

The text literal is data type 96 (char), while both bind variables are type 1 (varchar/varchar2); but notice the char_value bind variable has the trailing space, with length 8 and the last character as code point 32.

When you compare your char column value with a varchar2 bind variable the column value is implicitly converted from char to varchar2:

The following rules govern implicit data type conversions:

  • During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable.

So your space-padded char(8) column value is implicitly converted to varchar2(8) to match the bind variable's data type, and then because they are varchar2 the nonpadded comparison semantics are used.

When you compare your char(8) column with the supposedly-char(8) bind variable, you're actually comparing with a padded varchar2(8) - but both the implicitly converted column value and the blank-padded bind variable are actually the same, both with the trailing space; '1234567 ' is the same as '1234567 ', so there is a match, even with nonpadded comparison semantics.

With the varchar2(8) bind variable the same thing happens, but now the bound value is not padded, and as you are using nonpadded comparison semantics to compare '1234567 ' with '1234567' - they are not the same, so there is no match, and no data is returned by the query.

As @a_horse_with_no_name said you should almost always use varchar2 rather than char. But if you must use it and are stuck with it then at least make sure you use the same data type for comparisons.

Upvotes: 1

Nicola
Nicola

Reputation: 314

You are right

SELECT * FROM ATABLE WHERE AFIELD = :value;

does not work with CHAR as you desire.

Anyway I have noticed that the following query works as you desire:

SELECT * FROM ATABLE WHERE AFIELD = &value;

If you use &value in several places, you can use &&value (double &) the first time (and &value elsewhere), in order to avoid to input the same value several times; when you have to change that value, you can undefine it with:

undef value;

Upvotes: 1

Related Questions