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

Question

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.

There is a little confusion in this question: Is the _table_ named "Value" or the _column_ (in both cases `value` is a bad name). and what is `value_tx`, it's not shown in your data. — René Vogt, Dec 12 '18 at 18:28
You could use WHERE TRIM(value_tx) IS NULL, as an empty TRIM results in Null in Oracle. — Yosh, Dec 12 '18 at 18:37
Sorry I accidentally put VALUE_TX for VALUE. The column name is VALUE — John Wick, Dec 12 '18 at 18:44
@Yosh so in this case it would be WHERE TRIM(VALUE_TX) IS NOT NULL . Correct? — John Wick, Dec 12 '18 at 18:45
@Yosh is there any way I can give you points since you response was correct? thanks again. — John Wick, Dec 12 '18 at 19:09
@JohnWick I'm glad i could help you. I made my comment an answer that you could accept as the answer. :) — Yosh, Dec 12 '18 at 19:36

score 3 · Answer 1 · edited Dec 12 '18 at 19:17

3

This would do:

select *
  from tablename
 WHERE LENGTH(TRIM(value)) > 0

I found a very interesting answer in another question that could be helpful:
empty string in oracle

edited Dec 12 '18 at 19:17

Allan

17,141
4
52
69

answered Dec 12 '18 at 18:26

forpas

160,666
10
38
76

weird. I do have entries in the table but when I run the query above I still return no entries. – John Wick Dec 12 '18 at 18:39
what is the name of the column? – forpas Dec 12 '18 at 18:41
`NVL(value_tx, '')` is pointless - empty string and null are the same in Oracle. And nothign is ever equal to or not equal to null, so `<> ''` is never true. – Alex Poole Dec 12 '18 at 18:43
@AlexPoole so you mean that `NVL(NULL, 'A')` returns `NULL`? – forpas Dec 12 '18 at 18:45
Sorry I accidentally put VALUE_TX for VALUE. The column name is VALUE. And @AlexPoole would just stating that TRIM(VALUE_TX) S NOT NULL be enough to cover all scenarios? What I'm trying to address is data being passed in (either as a blank , space or null). I want to filter those out. – John Wick Dec 12 '18 at 18:45
And the name of the table? – forpas Dec 12 '18 at 18:46
@forpas - no, but `NVL(NULL, '')` returns `NULL`. So this will still not return any data. In Oracle, you have to do what @Yosh said. – Alex Poole Dec 12 '18 at 18:48
Are you sure about that? Even so, a logical statement that validates to NULL isn't treated as FALSE, meaning that in this case it will not return the row. Correct me if I'm wrong – forpas Dec 12 '18 at 18:51
@JohnWick of course try all the suggestions to solve your problem. I believe `TRIM(NVL(value, '')) <> ''` is enough if there are any data in the column. – forpas Dec 12 '18 at 18:53
@forpas - the comparison is unknown rather than false, but it still isn't true. Your query doesn't find any data; @Yosh's does (whether you use `is null` or `is not null`). [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=8c86ec98758b9b26881cceef9e7e227c) including both your answers. – Alex Poole Dec 12 '18 at 19:04
Yes I see that now that's why I posted an alternative that according to my testing works. – forpas Dec 12 '18 at 19:06
thanks everyone :) and yes @Yosh 's solution is correct. – John Wick Dec 12 '18 at 19:08
@AlexPoole so the conclusion is that oracle evaluates `''` to `NULL`? – forpas Dec 12 '18 at 19:12
1

@forpas - it's not really a conclusion, it's the documented behaviour - ["Oracle Database treats a character value with a length of zero as null"](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Nulls.html). That also shows how to compare with null. – Alex Poole Dec 12 '18 at 19:14
1

@AlexPoole I learnt something new and interesting today, that if missed can cause trouble. – forpas Dec 12 '18 at 19:16

score 2 · Accepted Answer · answered Dec 12 '18 at 19:34

2

You could query

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

since an empty TRIM results in NULL in Oracle.

Regards

answered Dec 12 '18 at 19:34

Yosh

706
5
15

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

2 Answers2