SQL CONCAT with IF statement

Question

I have it:

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

How can I change it so that if the initials field is null, it also does not include a 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 instance:

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

This will return the string "First Last" with no spaces anywhere except one CONCAT_WS, between two strings that are not NULL.

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

In your case, it will 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.