Bdtjtk

In Oracle SQL, I'm attempting to concatenate an address from several fields that look like this

CREATE TABLE sql_test_c 

( 

    ADDRESS_LINE varchar(255), 

    ADDRESS_LINE_2 varchar(255), 

    ADDRESS_LINE_3 varchar(255),

    CITY_NAME varchar(255),

    STATE_CODE varchar(255),

    ZIP_CODE varchar(255)

); 



INSERT INTO sql_test_c (ADDRESS_LINE, ADDRESS_LINE_2, CITY_NAME, STATE_CODE, ZIP_CODE) VALUES ('Business Name', '123 Main Street', 'Seattle', 'WA', '12345');

Unfortunately, in my dataset there are a lot of addresses that don't have anything in ADDRESS_LINE_3 (like this one) so when I use

SELECT ADDRESS_LINE || ', ' || 

       ADDRESS_LINE_2 || ', ' ||

       ADDRESS_LINE_3 || ', ' ||

       CITY_NAME || ', ' ||

       STATE_CODE || ' ' ||

       ZIP_CODE

FROM sql_test_c;

I end up with

Business Name, 123 Main Street, , Seattle, WA 12345

With those two commas between the address and the City. Is there a way to include columns dynamically if they are null or not? I've tried various forms of coalesce like this one:

SELECT ADDRESS_LINE || ', ' || 

       ADDRESS_LINE_2 || ', ' ||

       COALESCE(ADDRESS_LINE_3 || ', ', '') ||

       CITY_NAME || ', ' ||

       STATE_CODE || ' ' ||

       ZIP_CODE

FROM sql_test_c;

But it doesn't seem to change anything

Your coalesce() doesn't work because Oracle treats NULL as an empty string with the || operator.

You can get around this using case:

SELECT ADDRESS_LINE || ', ' || 

       ADDRESS_LINE_2 || ', ' ||

       (CASE WHEN ADDRESS_LINE_3 IS NOT NULL THEN ADDRESS_LINE_3 || ', ' END) ||

       CITY_NAME || ', ' ||

       STATE_CODE || ' ' ||

       ZIP_CODE

FROM sql_test_c;