Why Oracle CHAR Queries Behave Differently: Hidden Space Padding Explained

Oracle’s CHAR datatype stores fixed‑length strings, padding the right side with spaces to match the defined length (e.g., char(10) stores 'kkk' as 'kkk       '). This legacy behavior can cause surprising results when querying CHAR columns.

1. Environment Setup

The experiments use Oracle Database 11g Release 11.2.0.4 (11gR2). A test table t is created with a CHAR(100) column chr_a and a VARCHAR2(100) column vchar_a, populated with 119,498 rows from dba_objects. Six rows contain the value SCOTT in both columns.

create table t (chr_a char(100), vchar_a varchar2(100));
insert into t select owner, owner from dba_objects;
commit;
exec dbms_stats.gather_table_stats(user,'T',cascade => true);

2. Constant WHERE Condition Test

When a literal constant is used in a WHERE clause, Oracle automatically pads the constant with spaces to match the CHAR length, so the query succeeds:

select count(*) from t where chr_a='SCOTT';   -- returns 6
select length(chr_a) from t where chr_a='SCOTT';   -- returns 100

Even if extra spaces are added manually, the result is unchanged because the optimizer treats the padded constant as equivalent:

select count(*) from t where chr_a='SCOTT   ';   -- returns 6

3. Function Operation Effect

Using rpad to pad the constant to the full length also works:

select count(*) from t where chr_a=rpad('SCOTT',100,' ');   -- returns 6

However, if the padding length is shorter than the column length, the query returns no rows:

select count(*) from t where chr_a=rpad('SCOTT',10,' ');   -- returns 0
select count(*) from t where chr_a=rpad('SCOTT',50,' ');   -- returns 0

This shows that the optimizer’s automatic space‑padding applies only when the right‑hand side is a literal CHAR; a function result is treated as VARCHAR2, so the padding rule is not triggered.

4. Bind Variable Handling

When a CHAR bind variable is used, Oracle applies the same automatic padding as with literals:

declare
  a char(100);
  cnt number;
begin
  a := 'SCOTT';
  execute immediate 'select count(*) from t where chr_a=:1' into cnt using a;
  dbms_output.put_line('Result Is : '||cnt);
end;
-- Result Is : 6

Even if the bind variable contains trailing spaces, the result remains 6. In contrast, a VARCHAR2 bind variable does not receive automatic padding, and the query returns 0:

declare
  a varchar2(100);
  cnt number;
begin
  a := 'SCOTT';
  execute immediate 'select count(*) from t where chr_a=:1' into cnt using a;
  dbms_output.put_line('Result Is : '||cnt);
end;
-- Result Is : 0

5. Conclusions

The Oracle optimizer automatically pads CHAR values with spaces during comparisons, allowing literals (or CHAR bind variables) to match even when extra spaces are present.

This automatic padding is only triggered when the compared value is of type CHAR; VARCHAR2 values, including results from functions like rpad, do not receive the padding.

Developers should be aware of these rules to avoid unexpected query results, especially when mixing CHAR and VARCHAR2 columns or using bind variables.