mySQL CONCAT_WS() for select lists

Source: Jopa - MyTechSolutions.com
Published: 2021-02-05

Sometimes you have to merge the contents of a couple of mySQL columns if they are not  NULL.

Enter CONCAT_WS() Concatenate With Separator. It allows you to concat or join a liat of columns on a separator like CONCAT() though the separator is listed first and it skips NULL values.

Here I was looking for a nice way to pull the records for categories that have content in them while returning select list friendly member names and category names. While CONCAT() may be enough, this drops NULL values and makes a nice clean list.

SELECT 
  content.id, 
  CONCAT_WS(' ', t1.firstname, t1.lastname) AS member_id, 
  CONCAT_WS(' ', t2.title, t2.title) AS category_ids, 
  content.title, 
  content.source, 
  content.active 
FROM content 

JOIN members AS t1 ON t1.id = content.member_id 
JOIN categories AS t2 ON t2.id = content.category_ids

 


add comment