How to remove the seperator if all values are NULL in MySQL CONCAT_WS?

Question

I am trying to use CONCAT_WS to generate a subject line. However, I want to ignore the seperator if a value is null.

Please look at this query

SELECT
CONCAT_WS(" ",
CASE WHEN total_attempts > 0 THEN
CONCAT( call_code_title , " - ", total_attempts, "<sup>",
    CASE WHEN total_attempts = 2 THEN "nd"
    WHEN total_attempts = 3 THEN "rd"
    ELSE "th" END
, "</sup> attempt") ELSE call_code_title END
, "-", program_name) AS callSubject
FROM table

The problem is when "program_name" IS NULL then I always will have "-" at the end of the string. I want to not concat "-" id program_name IS NULL

How do I do that?

Thanks

Answer 1

IFNULL(program_name, concat("-", program_name))

SELECT
CONCAT_WS(" ",
CASE WHEN total_attempts > 0 THEN
CONCAT( call_code_title , " - ", total_attempts, "<sup>",
    CASE WHEN total_attempts = 2 THEN "nd"
    WHEN total_attempts = 3 THEN "rd"
    ELSE "th" END
, "</sup> attempt") ELSE call_code_title END
, IFNULL(program_name, concat("-", program_name)) , " " ) AS callSubject
FROM table

Answer 2

SELECT
CONCAT_WS(" ",
CASE WHEN total_attempts > 0 THEN
CONCAT( call_code_title , " - ", total_attempts, "<sup>",
    CASE WHEN total_attempts = 2 THEN "nd"
    WHEN total_attempts = 3 THEN "rd"
    ELSE "th" END
, "</sup> attempt") ELSE call_code_title END
, CONCAT("-", program_name) ) AS callSubject
FROM table

If program_name is NULL, then CONCAT("-", program_name) will also return NULL, which CONCAT_WS will handle correctly by ignoring what it sees as a single null argument.

CONCAT() returns NULL if any argument is NULL.

^{— http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_concat}