How to conditionally exclude a column from a concatenation? [duplicate]












0
















This question already has an answer here:




  • SQL using If Not Null on a Concatenation

    8 answers




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










share|improve this question













marked as duplicate by Robert Harvey Jan 3 at 19:50


This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.



















  • I wouldn't do that; it makes life hell for the downstream person or code to process the result, as they now have to work out how many addresses are in the CSV. Just leave the column empty.

    – Robert Harvey
    Jan 3 at 19:38













  • See stackoverflow.com/questions/37330260/…

    – Josh Eller
    Jan 3 at 19:40











  • @RobertHarvey this is going to a UI so it looks bad to have , , in the middle of the address.

    – rdennis42
    Jan 3 at 19:48











  • @JoshEller that answer works great thank you

    – rdennis42
    Jan 3 at 19:49
















0
















This question already has an answer here:




  • SQL using If Not Null on a Concatenation

    8 answers




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










share|improve this question













marked as duplicate by Robert Harvey Jan 3 at 19:50


This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.



















  • I wouldn't do that; it makes life hell for the downstream person or code to process the result, as they now have to work out how many addresses are in the CSV. Just leave the column empty.

    – Robert Harvey
    Jan 3 at 19:38













  • See stackoverflow.com/questions/37330260/…

    – Josh Eller
    Jan 3 at 19:40











  • @RobertHarvey this is going to a UI so it looks bad to have , , in the middle of the address.

    – rdennis42
    Jan 3 at 19:48











  • @JoshEller that answer works great thank you

    – rdennis42
    Jan 3 at 19:49














0












0








0









This question already has an answer here:




  • SQL using If Not Null on a Concatenation

    8 answers




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










share|improve this question















This question already has an answer here:




  • SQL using If Not Null on a Concatenation

    8 answers




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





This question already has an answer here:




  • SQL using If Not Null on a Concatenation

    8 answers








sql oracle11g






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jan 3 at 19:37









rdennis42rdennis42

156




156




marked as duplicate by Robert Harvey Jan 3 at 19:50


This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.









marked as duplicate by Robert Harvey Jan 3 at 19:50


This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.















  • I wouldn't do that; it makes life hell for the downstream person or code to process the result, as they now have to work out how many addresses are in the CSV. Just leave the column empty.

    – Robert Harvey
    Jan 3 at 19:38













  • See stackoverflow.com/questions/37330260/…

    – Josh Eller
    Jan 3 at 19:40











  • @RobertHarvey this is going to a UI so it looks bad to have , , in the middle of the address.

    – rdennis42
    Jan 3 at 19:48











  • @JoshEller that answer works great thank you

    – rdennis42
    Jan 3 at 19:49



















  • I wouldn't do that; it makes life hell for the downstream person or code to process the result, as they now have to work out how many addresses are in the CSV. Just leave the column empty.

    – Robert Harvey
    Jan 3 at 19:38













  • See stackoverflow.com/questions/37330260/…

    – Josh Eller
    Jan 3 at 19:40











  • @RobertHarvey this is going to a UI so it looks bad to have , , in the middle of the address.

    – rdennis42
    Jan 3 at 19:48











  • @JoshEller that answer works great thank you

    – rdennis42
    Jan 3 at 19:49

















I wouldn't do that; it makes life hell for the downstream person or code to process the result, as they now have to work out how many addresses are in the CSV. Just leave the column empty.

– Robert Harvey
Jan 3 at 19:38







I wouldn't do that; it makes life hell for the downstream person or code to process the result, as they now have to work out how many addresses are in the CSV. Just leave the column empty.

– Robert Harvey
Jan 3 at 19:38















See stackoverflow.com/questions/37330260/…

– Josh Eller
Jan 3 at 19:40





See stackoverflow.com/questions/37330260/…

– Josh Eller
Jan 3 at 19:40













@RobertHarvey this is going to a UI so it looks bad to have , , in the middle of the address.

– rdennis42
Jan 3 at 19:48





@RobertHarvey this is going to a UI so it looks bad to have , , in the middle of the address.

– rdennis42
Jan 3 at 19:48













@JoshEller that answer works great thank you

– rdennis42
Jan 3 at 19:49





@JoshEller that answer works great thank you

– rdennis42
Jan 3 at 19:49












1 Answer
1






active

oldest

votes


















3














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;





share|improve this answer
























  • This answer works, although DECODE(ADDRESS_LINE_3, NULL, '', ', ' || ADDRESS_LINE_3) does the same thing slightly cleaner

    – rdennis42
    Jan 3 at 19:50













  • @rdennis42 . . . I for one strongly disagree. decode() is a bespoke Oracle function. CASE is the standard SQL syntax for conditional expressions. I generally much prefer standards.

    – Gordon Linoff
    Jan 3 at 22:28


















1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









3














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;





share|improve this answer
























  • This answer works, although DECODE(ADDRESS_LINE_3, NULL, '', ', ' || ADDRESS_LINE_3) does the same thing slightly cleaner

    – rdennis42
    Jan 3 at 19:50













  • @rdennis42 . . . I for one strongly disagree. decode() is a bespoke Oracle function. CASE is the standard SQL syntax for conditional expressions. I generally much prefer standards.

    – Gordon Linoff
    Jan 3 at 22:28
















3














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;





share|improve this answer
























  • This answer works, although DECODE(ADDRESS_LINE_3, NULL, '', ', ' || ADDRESS_LINE_3) does the same thing slightly cleaner

    – rdennis42
    Jan 3 at 19:50













  • @rdennis42 . . . I for one strongly disagree. decode() is a bespoke Oracle function. CASE is the standard SQL syntax for conditional expressions. I generally much prefer standards.

    – Gordon Linoff
    Jan 3 at 22:28














3












3








3







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;





share|improve this answer













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;






share|improve this answer












share|improve this answer



share|improve this answer










answered Jan 3 at 19:40









Gordon LinoffGordon Linoff

793k36318421




793k36318421













  • This answer works, although DECODE(ADDRESS_LINE_3, NULL, '', ', ' || ADDRESS_LINE_3) does the same thing slightly cleaner

    – rdennis42
    Jan 3 at 19:50













  • @rdennis42 . . . I for one strongly disagree. decode() is a bespoke Oracle function. CASE is the standard SQL syntax for conditional expressions. I generally much prefer standards.

    – Gordon Linoff
    Jan 3 at 22:28



















  • This answer works, although DECODE(ADDRESS_LINE_3, NULL, '', ', ' || ADDRESS_LINE_3) does the same thing slightly cleaner

    – rdennis42
    Jan 3 at 19:50













  • @rdennis42 . . . I for one strongly disagree. decode() is a bespoke Oracle function. CASE is the standard SQL syntax for conditional expressions. I generally much prefer standards.

    – Gordon Linoff
    Jan 3 at 22:28

















This answer works, although DECODE(ADDRESS_LINE_3, NULL, '', ', ' || ADDRESS_LINE_3) does the same thing slightly cleaner

– rdennis42
Jan 3 at 19:50







This answer works, although DECODE(ADDRESS_LINE_3, NULL, '', ', ' || ADDRESS_LINE_3) does the same thing slightly cleaner

– rdennis42
Jan 3 at 19:50















@rdennis42 . . . I for one strongly disagree. decode() is a bespoke Oracle function. CASE is the standard SQL syntax for conditional expressions. I generally much prefer standards.

– Gordon Linoff
Jan 3 at 22:28





@rdennis42 . . . I for one strongly disagree. decode() is a bespoke Oracle function. CASE is the standard SQL syntax for conditional expressions. I generally much prefer standards.

– Gordon Linoff
Jan 3 at 22:28





Popular posts from this blog

Monofisismo

Angular Downloading a file using contenturl with Basic Authentication

Olmecas