2 removed the unnecessary WHERE part
source | link

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
WHERE (vendor_address1 || vendor_address2 || vendor_city || vendor_state || vendor_zip_code) IS NOT NULL
ORDER BY vendor_name;

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

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
WHERE (vendor_address1 || vendor_address2 || vendor_city || vendor_state || vendor_zip_code) IS NOT NULL
ORDER BY vendor_name;

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

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.

1
source | link

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
WHERE (vendor_address1 || vendor_address2 || vendor_city || vendor_state || vendor_zip_code) IS NOT NULL
ORDER BY vendor_name;

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