Query Multiple Columns where one or more columns have a value

Question

I am trying to query a table on an Oracle Database. It has 4 columns:

ID (Number)
EMAIL (Varchar)
PHONE (Varchar)
FAX (Varchar).

The ID is a unique identifier and is required. The 3 remaining columns can either have a value, can be NULL or can be an empty string. So, something like:

| ID | EMAIL          | PHONE        | FAX            |
| -- | -----------    | ------------ | -------------- |
| 10 | na@na.ca       |888-888-8888  | NULL           |
| 11 |                |NULL          |                |
| 12 | buiness@biz.ca |              | 666-666-6666   |
| 13 |                |NULL          | 555-555-5555   |
| 14 |NULL            |              | NULL           |
| 15 |NULL            |222-222-2222  | NULL           |

I am trying to build a query that only eliminates rows that have no actual value across EMAIL, PHONE or FAX (i.e. a row that does not contain either an email address, a phone number or a fax number). Examples in the table above are row with ID's 11 and 14. So, if there is at least one of or all of an email, phone or fax, I want to include those rows in my query.

I have played around with a couple queries, but I cannot seem to seem to get the results I want. I had thought this was going to be easy, but maybe it's just late in the day. The latest incarnation of my query is something along the lines of:

SELECT c.id, c.email, c.fax, c.phone
FROM 
  table c
where
  (c.email is not NULL or c.email <> '') or
  (c.fax is not NULL or c.fax <> '') or
  (c.phone is not NULL or c.phone <> '')

The above query only seems to result in rows that have all 3 values. I've tinkered the query a bit with some 'where not' clauses and a mixture of 'or's' and 'and's', however I am not getting what I want. Anything to help would be appreciated.

I'm a little confused by the question. Oracle treats empty strings and NULL values as the same. Is the "NULL" really `'NULL'`? — Gordon Linoff, Feb 17 '21 at 01:30
@Gordon Linoff You have answered a question or 2 of mine in the past and always seem to provide good insight. I tested your theory on one of my columns (i.e. TRIM(c.email) IS NULL ) and found that many of the 'empty' cells have quite a few white spaces in them, which explains why my query wasn't picking up on that. Thanks for your suggestion. If you submit an answer to my question using an option to remove white spaces, I'll accept the answer and upvote. Cheers. — Mike, Feb 17 '21 at 16:43

score 1 · Answer 1 · answered Feb 17 '21 at 01:13

1

SELECT c.id, c.email, c.fax, c.phone
FROM 
  table c
where NOT
(coalesce(email,'')='' and coalesce(fax,'')='' and coalesce(phone,'')='')

answered Feb 17 '21 at 01:13

LoztInSpace

5,584
1
15
27

score 1 · Answer 2 · answered Feb 17 '21 at 01:15

1

Is "null" actually in there as a string value in some occasions (such as fax for ID 10)? If so, this query works:

    select id, email, phone, fax
    from table_name
    where 
     (email is not null and email <> 'NULL') or
     (fax is not null and fax <> 'NULL') or
     (phone is not null and phone <> 'NULL')

answered Feb 17 '21 at 01:15

Isolated

5,169
1
6
18

Great answer. See my comment in the original post to see what caused my issue. Thanks – Mike Feb 17 '21 at 16:46

Heron Fonsaca · Answer 3 · 2021-02-17T20:05:27.160

1

It seems that empty varchar values are treated as NULL in Oracle as this answer points.

So, one could just compare string against nullables and reverse the query result of all null values.

Given that some columns have many blank spaces in them as commented:

SELECT * FROM example c
WHERE NOT (
  TRIM(c.email) IS NULL AND 
  TRIM(c.phone) IS NULL AND
  TRIM(c.fax) IS NULL
)

edited Feb 17 '21 at 20:05

answered Feb 17 '21 at 01:18

Heron Fonsaca

46
6

Great answer. See my comment in the original post to see what caused my issue. Thanks – Mike Feb 17 '21 at 16:46
Your welcome, Mike! I've edited the answer to cover the many blank spaces case. – Heron Fonsaca Feb 18 '21 at 02:04

score 0 · Answer 4 · answered Feb 17 '21 at 01:08

I think you can do union with each of the criteria in your where clause. So something like this:

(SELECT * FROM C WHERE email is not NULL or email <> '') UNION
(SELECT * FROM C WHERE fax is not NULL or fax <> '') UNION
(SELECT * FROM C WHERE phone is not NULL or phone <> '')

score 0 · Answer 5 · answered Feb 17 '21 at 03:25

Instead of selecting columns not with null or empty string you need to select those with null value or empty string. And between two conditions you need to use and instead of or since you need to select rows where all three columns are null or empty. please try below query

SELECT c.id, c.email, c.fax, c.phone FROM table c where (c.email is NULL or c.email '=') and (c.fax is NULL or c.fax = '') and (c.phone is NULL or c.phone = '')

Query Multiple Columns where one or more columns have a value

5 Answers5