Oracle 10g: column is blank, but is not null or an empty string

Question

I have a column in a table that is blank. The weird thing is that it does not appear to be null or an empty string. So, if I do this:

SELECT * 
  FROM TABLE 
 WHERE column IS NULL

...or:

SELECT * 
  FROM TABLE 
 WHERE column = ''

I get nothing. Thoughts?

Answer 1

Issue this query:

SELECT column, DUMP(column, 1016)
FROM table

It'll show the exact contents.

Related: Oracle does not allow empty strings; they're silently converted to NULL.

Answer 2

Maybe the column contains only spaces?

Did you try

select * 
from table 
where trim(column) is null

Answer 3

Oracle's got a basically permanent annoyance that empty strings are treated as null. However, are you sure that's an empty string? It could be an otherwise invisible character, such as a space or tab/linebreak/linefeed/etc... What does the string length show when you do select length(column) from table?

Answer 4

Try this:

SELECT *    
  FROM TABLE   
 WHERE TRIM(column) IS NULL