CODEWITHSUNDEEP
sqloracle-database
Josh
Josh

Reputation: 3611

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

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?

Upvotes: 10

Views: 45104

Answers (5)

Álvaro González
Álvaro González

Reputation: 146660

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.

Upvotes: 16

Benoit
Benoit

Reputation: 79243

If your column is not VARCHAR2 but CHAR(N) then insertion of an empty string is padded. See what Tom Kyte tells about this

Upvotes: 1

Chandu
Chandu

Reputation: 82963

Try this:

SELECT *    
  FROM TABLE   
 WHERE TRIM(column) IS NULL

Upvotes: 2

Marc B
Marc B

Reputation: 360922

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?

Upvotes: 3

user330315
user330315

Reputation:

Maybe the column contains only spaces?

Did you try

select * 
from table 
where trim(column) is null

Upvotes: 16

Related Questions