SQL CONCAT with an IF statement

Question

I have this:

SELECT CONCAT(forename,' ',IFNULL(initials, ''),' ',surname) AS name FROM users

How do I change it so that if the initials field is null it also doesn't include the space after it?

Answer 1

SELECT CONCAT(forename,' ',IFNULL(CONCAT(initials,' '), ''),surname) AS name FROM users

Answer 2

Use SELECT CONCAT(forename, ' ', CASE WHEN initials IS NULL THEN '' ELSE initials || ' ' END, surname) ...

Answer 3

I would use CONCAT_WS. For example:

SELECT CONCAT_WS(' ', NULL, 'First', NULL, 'Last', NULL);

This will return the string "First Last" with no spaces anywhere other than one CONCAT_WS has put between the two strings that are not NULL.

The first argument of CONCAT_WS is the glue that appears between the non-NULL values.

In your case this would be:

SELECT CONCAT_WS(' ', forename, initials, surname) AS name FROM users;

From here:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

Edit: This only works in MySQL.