CODEWITHSUNDEEP
mysql
Houdmont
Houdmont

Reputation: 1323

Conditional CONCAT with potentially NULL or empty values

In the below piece of code, I am creating an Address field by concatenating various parts of an address.

However, if for instance address2 was empty, the trailing , will still be concatenated into Address.

This means if all fields were empty, I end up with a result of ,,,,.

If address1 is "House Number" and everything else is empty, I end up with House Number,,,,.

CONCAT( COALESCE(address1,'')   ,   ', '    , 
        COALESCE(address2,'')   ,   ', '    , 
        COALESCE(address3,'')   ,   ', '    , 
        COALESCE(city,'')       ,   ', '    , 
        COALESCE(zip, '')
) AS Address,

Is there some way of conditionally placing the commas between address parts only if the content of an address part is not empty.

Such as something along the lines of (pseudo-code) IF(address1) is NULL use '' ELSE use ','

Thank you.

Upvotes: 35

Views: 38456

Answers (4)

Guest
Guest

Reputation: 101

CONCAT_WS(', ',
        NULLIF(`address1`,''),
        NULLIF(`address2`,''),
        NULLIF(`address3`,''),
        NULLIF(`city`,''),
        NULLIF(`zip`,'')
)
  • CONCAT_WS combines non-NULL strings.
  • NULLIF writes NULL if left and right side are equals. In this case if values are equals an empty sting ''.

Upvotes: 6

Martin
Martin

Reputation: 671

CONCAT_WS(', ',
        IF(LENGTH(`address1`),`address1`,NULL),
        IF(LENGTH(`address2`),`address2`,NULL),
        IF(LENGTH(`address3`),`address3`,NULL),
        IF(LENGTH(`city`),`city`,NULL),
        IF(LENGTH(`zip`),`zip`,NULL)
)

Upvotes: 67

xkeshav
xkeshav

Reputation: 54016

try with MAKE_SET

SELECT MAKE_SET(11111,`address1`,`address2`,`address3`,`city`,`zip`) AS Address

It will returns a string with all NOT NULL value separated by ,

Upvotes: 6

Bojangles
Bojangles

Reputation: 101473

Using CONCAT_WS as Mat says is a very good idea, but I thought I'd do it a different way, with messy IF() statements:

CONCAT( COALESCE(address1,''), IF(LENGTH(address1), ', ', ''), 
        COALESCE(address2,''), IF(LENGTH(address2), ', ', ''), 
        COALESCE(address3,''), IF(LENGTH(address3), ', ', ''), 
        COALESCE(city,''), IF(LENGTH(city), ', ', ''), 
        COALESCE(zip,''), IF(LENGTH(address1), ', ', ''), 
) AS Address,

The IF()s check if the field has a length and if so returns a comma. Otherwise, it returns an empty string.

Upvotes: 5

Related Questions