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.
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
' ', so that you could say
select * from mat where matnr='test' and NVL(A, ' ') <> ' '