ORACLE | SUBSTR function not working

Question

I'm having an issue regarding SUBSTR function.

Imagine the table (TABLE1):

ABC_DEF   DESCRIPTION
--------  -------------
0999      TEST INFO

If I do this query:

SELECT * FROM TABLE1 
WHERE 
(
     ABC_DEF = '0999'
)

I get this results:

ABC_DEF   DESCRIPTION
--------  -------------
0999      TEST INFO

However if I do this:

SELECT * FROM TABLE1 
WHERE 
(
     ABC_DEF = SUBSTR('00000999', 5, 4)
)

I get 0 results. I don't understand the behavior at all. The attribute ABC_DEF is a char(8).

Answer 1

A CHAR(8) column will right-pad the value with space (CHR(32)) characters until it has a length of 8.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table1 ( ABC_DEF CHAR(8), DESCRIPTION VARCHAR2(20) );

INSERT INTO table1 VALUES ( '0999', 'TEST INFO' );

Query 1:

SELECT * FROM TABLE1 
WHERE ABC_DEF = '0999'

Results:

|  ABC_DEF | DESCRIPTION |
|----------|-------------|
| 0999     |   TEST INFO |

Query 2:

SELECT * FROM TABLE1 
WHERE ABC_DEF = SUBSTR('00000999', 5, 4)

Results:

Query 3:

SELECT * FROM TABLE1 
WHERE ABC_DEF = RPAD( SUBSTR('00000999', 5, 4), 8, ' ' )

Results:

|  ABC_DEF | DESCRIPTION |
|----------|-------------|
| 0999     |   TEST INFO |