CONCAT values in MySQL query to ignore NULL values

Question

I want to CONCAT the values in 8 fields (sp, lp, gp, sr, zd, md, pr, rs), and return the result as "chemistry". The problem is that any one record will only have two (maybe three or four) values out of the 8 possible, the remaining values will be NULL. Furthermore, if the value is NULL, I want neither the preceding text (SP:, LP:, GP:, SR:, etc...), nor the (line break) to be displayed.

From my research on here I've come across CONCAT_WS(), and IFNULL(). I will need help implementing these functions, in order to achieve my desired result.

    CONCAT(
      'SP: ', sp, '<br />',
      'LP: ', lp, '<br />',
      'GP: ', gp, '<br />',
      'SR: ', sr, '<br />',
      'ZD: ', zd, '<br />',
      'MD: ', md, '<br />',
      'PR: ', pr, '<br />',
      'RS: ', rs
    ) AS chemistry

Answer 1

You can use two facts here: first, CONCAT (as many other SQL functions) returns NULL if any argument is NULL; second, CONCAT_WS will just skip NULL values.

That makes the query as simple as...

CONCAT_WS('<br />',
   CONCAT('SP:', sp),
   CONCAT('LP:', lp),
   ...
) AS chemistry

---

Having said all this, I really wonder is it necessary to make this formatting at the query (database) level. First, using <br /> to separate elements is really just a detail of representation - and it is subject to change far more often than you'd like it to. Second, it might be far easier both to write and adjust this code at application level - by using loops, for example.