CODEWITHSUNDEEP
mysqlsql
mb1987
mb1987

Reputation: 467

Concatenation of columns mysql

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.

Upvotes: 0

Views: 183

Answers (2)

juergen d
juergen d

Reputation: 204746

You can use COALESCE

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

or better CONCAT_WS

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

Upvotes: 0

Joseph B
Joseph B

Reputation: 5669

Try:

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

Upvotes: 1

Related Questions