'condition where a <> ' ' and a <> '' not working

I wrote a SQL to query table mat from an oracle db where column A is not null. Column A is varchar and its default value is ' '. I wrote the sql below:

select * from mat where matnr='test' and A <>'' and A <> ' '

But it return an empty data set.

Then I ran:

select * from mat where matnr='test' and A <> ' '

This query worked. So what is the reason? Thx.

sqloracle


Solution 1:[1]

In Oracle, '' means NULL. Any direct comparison to NULL returns NULL instead of TRUE or FALSE, so you cannot say A <> '' - you must say A IS NOT NULL.

Another possibility would be to use the NVL function, replacing NULL with ' ', so that you could say

select * from mat where matnr='test' and NVL(A, ' ') <> ' '

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bob Jarvis - Слава Україні

Related Questions