CODEWITHSUNDEEP
mysql
John Magnolia
John Magnolia

Reputation: 16803

MySQL CONCAT address into string seperated by commar

Is there any way to shorten/easier read the following SQL to concatenate a string.

SELECT
   CONCAT(       
    IF(location_address1 != '',
       CONCAT(location_address1, ", "),
       ""
    ),
    IF(location_address2 != '',
       CONCAT(location_address2, ", "),
       ""
    ),
    IF(location_town != '',
       CONCAT(location_town, ", "),
       ""
    ),
    IF(location_region != '',
       CONCAT(location_region, ", "),
       ""
    ),
    IF(location_postcode != '',
       CONCAT(location_postcode, ", "),
       ""
    ),
    c.country_name
   )      
FROM
   countries c      
WHERE
   c.country_id = locations.country_id LIMIT 1

Upvotes: 1

Views: 1102

Answers (4)

Rikin Patel
Rikin Patel

Reputation: 9383

as per @Xdazz

This Trim Version:

SELECT 
  CONCAT_WS(', ', 
       NULLIF(Trim(location_address1), ''),
       NULLIF(Trim(location_address2), ''),
       NULLIF(Trim(location_town), ''), 
       NULLIF(Trim(location_region), ''), 
       NULLIF(Trim(location_postcode), ''), 
       NULLIF(Trim(country_name), '')
  ) AS address
FROM
   countries c      
WHERE
   c.country_id = locations.country_id LIMIT 1

Upvotes: 2

Omesh
Omesh

Reputation: 29101

you can use CONCAT_WS function in MySQL as:

SELECT CONCAT_WS(',',IF(location_address1 = '', NULL, location_address1),
                     IF(location_address2 = '', NULL, location_address2),
                     IF(location_town = '', NULL, location_town),
                     IF(location_region = '', NULL, location_region),
                     IF(location_postcode = '', NULL, location_postcode),
                     c.country_name)  AS str
FROM countries c
WHERE c.country_id = locations.country_id
LIMIT 1;

or even simple approach suggested by @CyberDem0n:

SELECT CONCAT_WS(',',NULLIF(location_address1,''),
                     NULLIF(location_address2,''),
                     NULLIF(location_town,''),
                     NULLIF(location_region,''),
                     NULLIF(location_postcode,''),
                     c.country_name)  AS str
FROM countries c
WHERE c.country_id = locations.country_id
LIMIT 1;

but it will be better approach if you can store NULL values instead of blank values.

Upvotes: 1

Dany Y
Dany Y

Reputation: 7031

SELECT
   REPLACE(CONCAT(IF(location_address1 != '',
       CONCAT(location_address1, ", "),location_address2,", ",location_town, ", ",location_region,", " , location_postcode , ", " ,c.country_name ),',,',',')    
FROM
   countries c      
WHERE
   c.country_id = locations.country_id LIMIT 1

I didn't check the syntax, but my idea is instead of checking each one if it's null or empty, why don't you always concatenate with "," and then replace all ",," with ",".

Upvotes: 1

xdazz
xdazz

Reputation: 160883

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

NULLIF(expr1,expr2) Returns NULL if expr1 = expr2 is true, otherwise returns expr1.

SO your query could be:

SELECT 
  CONCAT_WS(', ', 
       NULLIF(location_address1, ''),
       NULLIF(location_address2, ''),
       NULLIF(location_town, ''), 
       NULLIF(location_region, ''), 
       NULLIF(location_postcode, ''), 
       NULLIF(country_name, '')
  ) AS address
FROM
   countries c      
WHERE
   c.country_id = locations.country_id LIMIT 1

Upvotes: 4

Related Questions