My query with to / NOT IN () clause not returning any values?

I have a table with the following data:

Value_ID      VALUE        CODE       HR       DATE      TYPE
   1           0           REG       01:00AM   1/1/18    HI
   2           4           REG       01:00AM   1/1/18    BYE
   3          null         REG       02:00AM   1/1/18    HELLO
   4                       REG       03:00AM   1/1/18    HI
   5           7           REG       04:00AM   1/1/18    BYE

I am trying to exclude any values that are blank (” or ‘ ‘); however when I try using a NOT IN clause or NOT EQUAL TO (<>) comparison, I return 0 entries. The query I am using is below:

select *
  from value
 WHERE value is not NULL
   AND value <> ' ' AND value

<> ”;

I’ve also tried:

 select *
   from value
  where value is not null
    and valuenot IN ('', ' ');

When I do that, the following is returned:

 Value_ID      VALUE        CODE       HR       DATE      TYPE

Is Oracle not liking this symbol for some reason?

Thanks in advance.

Answer

You could query

SELECT *
FROM your_table
WHERE TRIM(value) IS NOT NULL;

since an empty TRIM results in NULL in Oracle.

Regards

Advertisement

Answer