How to conditionally exclude a column from a concatenation? [duplicate]
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
sql oracle11g
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.
add a comment |
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
sql oracle11g
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
add a comment |
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
sql oracle11g
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
sql oracle11g
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
add a comment |
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
add a comment |
1 Answer
1
active
oldest
votes
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;
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
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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;
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
add a comment |
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;
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
add a comment |
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;
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;
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
add a comment |
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
add a comment |
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