How to omit multiple columns where is not null?

Question

I've constructed the below query which satisfies the requirements, except output includes NULL values between all "vendor_address2" columns, which isn't intended. I suspect my error is somewhere within the WHERE clause, which is designed to exclude any NULL values in the resulting output. I've included a copy of the corresponding table below the output for my most recent query:

SELECT vendor_name,
 vendor_address1 || ' ' || vendor_address2 || ' ' || vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS "Complete Address"
FROM ap.vendors
WHERE (vendor_address1 || vendor_address2 || vendor_city || vendor_state || vendor_zip_code) IS NOT NULL
ORDER BY vendor_name;

The resulting output:

enter image description here

Here is the corresponding table-view:

enter image description here

Thank you.

• Jack A.: What are you trying to do with the WHERE clause? Are you trying to exclude the rows where any of those columns are null?
• alexanderjsingleton: @JackA., I'm trying to display all contact information but omitting/excluding any NULL values. Thank you.

Answer 1

The screenshot of your data and output clearly shows the problem. The concatenation operator automatically takes care of NULL and you don't need to do anyhting. But you don't have NULL in your tables, you have 'NULL', a string, in your tables.

SQL> select 'A' || ' ' || null || ' ' || 'B' from dual;
'A'|
----
A B
SQL> select 'A' || ' ' || 'NULL' || ' ' || 'B' from dual;
'A'||''|
--------
A NULL B

If you really had NULL in your table, SQL Developer would display (null) in the data grid.

Clean your data, then try again.

update ap.vendors set vendor_address2 = null where vendor_address2 = 'NULL';

Alternatively, you can turn string 'NULL' to a real NULL on the fly:

SELECT vendor_name,
 vendor_address1 || ' ' || decode(vendor_address2, 'NULL', null, vendor_address2) || ' ' || vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS "Complete Address"
FROM ap.vendors
ORDER BY vendor_name;

But don't do that. Take care of your data.

• alexanderjsingleton: I appreciate your submission, but I don't think the problem is with the data. I believe the desired output can be treated by a specific query-perhaps a CASE statement? Thank you.
• Balazs Papp: @alexanderjsingleton I have already responded to that part, see the above SELECT with the DECODE. And yes, the problem is with the data.
• alexanderjsingleton: thank you; I tried again, and there is only instance that is displaying NULL- evidently because there is no 'vendor_address1' or 'vendor_address2' to display.

Answer 2

The WHERE clause will only exclude full rows, not individual columns. For dealing with individual columns, you need to use something like NVL or Case expressions.

I don't have an Oracle instance to test against right now, but something strikes me as odd in your data. Nulls in Oracle are supposed to concatenate to empty strings. Is it possible that your vendor_address2 column actually contains the string 'NULL' instead of a null value?

• alexanderjsingleton: perhaps vendor_address2 may contain those strings; if so, how would I treat those instances?