Concatenation of columns mysql

Question

I have a table of addresses in a database containing USA zip codes

the columns in table are called zip5 and zip4

I want to combine these zip codes with - in between that

I am currently using mysql the query i am using is

select zip5,zip4, concat(zip5,'-',zip4) zip from address limit 500;

now there are certain occasions where zip4 column is null but there is data in zip5 column

in this situation new concatenated column call zip becomes null.

I dont want this to happen. if zip4 does not have data at least it should show data from zip5 with no - in between that.

possible duplicate of [MySQL - Concatenation](http://stackoverflow.com/questions/5975958/mysql-concatenation) — Chris Bier, Aug 01 '14 at 18:20
@ChrisB That question relates to the incorrect use of + for concatenation. — Joseph B, Aug 01 '14 at 18:23

score 1 · Answer 1 · answered Aug 01 '14 at 18:17

1

Try:

SELECT
    CASE 
        WHEN zip4 IS NULL THEN zip5
        ELSE concat(zip5,'-',zip4) 
    END zip 
from address limit 500;

answered Aug 01 '14 at 18:17

Joseph B

score 0 · Accepted Answer · answered Aug 01 '14 at 18:17

0

You can use COALESCE

select zip5, 
       zip4,
       concat(zip5,'-',coalesce(zip4,'')) zip 
from address

select zip5, 
       zip4,
       CONCAT_WS('-', zip5, zip4) zip 
from address

answered Aug 01 '14 at 18:17

juergen d

2 Answers2