Mysql combine columns if not null

Question

I know that I can combine multiple columns in mysql by doing:

SELECT CONCAT(zipcode, ' - ', city, ', ', state) FROM Table;

However, if one of the fields is NULL then the entire value will be NULL. So to prevent this from happening I am doing:

SELECT CONCAT(zipcode, ' - ', COALESE(city,''), ', ', COALESCE(state,'')) FROM Table;

However, there still can be situation where the result will look something like this:

zipcode-, ,

Is there a way in MySQL to only have to comma and the hyphen if the next columns are not NULL?

Aaron Morefield · Accepted Answer · 2019-12-07 01:13:25Z

There is actually a native function that will do this called Concat with Separator (concat_ws)!

Specifically, it seems that what you would need is:

SELECT CONCAT_WS(' - ',zipcode, NULLIF(CONCAT_WS(', ',city,state),'')) FROM TABLE;

This should account for all of the null cases you allude to.

However, it is important to note that a blank string ('') is different than a NULL. If you want to address this in the state/city logic you would add a second NULLIF check inside the second CONCAT_ws for the case where a city or a state would be blank strings. This will depend on the database's regard for the blank field and whether you are entering true NULLS into your database or checking the integrity of the blank data before you use it. Something like the following might be slightly more robust:

SELECT CONCAT_WS(' - ', zipcode, NULLIF(CONCAT_WS(', ', NULLIF(city, ''), NULLIF(state, '')), '')) FROM TABLE;

For more, check out the native documentation on concat_ws() here: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat-ws

nbk · Accepted Answer · 2019-12-07 00:08:28Z

2

I think you need something like this

Set @zipcode := '12345';
Set @city := NULL;
Set @state := NULL;
SELECT CONCAT(@zipcode, ' - ', COALESCE(@city,''), ', ', COALESCE(@state,''));

Result: 12345 - ,

SELECT CONCAT(@zipcode, IF(@city is NULL,'', CONCAT(' - ', @city)), IF(@state is NULL,'', CONCAT(', ',@state)))

Result 12345

answered Dec 7, 2019 at 0:08

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Comments

Nick · Accepted Answer · 2019-12-07 00:13:16Z

1

You need to make the output of the separators conditional on the following values being non-NULL. For example:

SELECT CONCAT(zipcode,
              CASE WHEN city IS NOT NULL OR state IS NOT NULL THEN ' - '
                   ELSE ''
              END,
              COALESCE(city, ''),
              CASE WHEN city IS NOT NULL AND state IS NOT NULL THEN ', '
                   ELSE ''
              END,
              COALESCE(state, '')
              ) AS address
FROM `Table``

Output (for my demo)

address
12345 - San Francisco, CA
67890 - Los Angeles 
34567 - AL
87654

Demo on dbfiddle

edited Dec 7, 2019 at 0:13

answered Dec 7, 2019 at 0:02

Nick

147k23 gold badges67 silver badges106 bronze badges

Collectives™ on Stack Overflow

Mysql combine columns if not null

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related