Which to use, concat, concat_ws? in mysql

Question

In PHP, I need to change this data

FirstName | MiddleName | LastName
---------------------------------
Robert    | Thomas     | Smith

Into the string "Smith, Robert Thomas"

I'm not sure if I need to use a join, concat or concat_ws.

I know that if I use

concat_ws(', ',LastName,FirstName,MiddleName)

Then I get "Smith, Robert, Thomas" but i need "Smith, Robert Thomas".

Any advice?

Answer 1

I realise this is a necro but it is worth noting the following:

Both CONCAT & CONCAT_WS are largely equivalent.

However, the most noteworthy difference is that CONCAT might not return the results you are expecting for cases where any of the inputs are NULL. In these cases CONCAT will return NULL whereas CONCAT_WS will skip NULL values and still return a string with the result of the remaining inputs. This means that in most cases you will probably want to use CONCAT_WS.

The following are equivalent except where one or more inputs are NULL:

// Result: LastName, FirstName, MiddleName
CONCAT( LastName, ', ', FirstName, ', ', MiddleName )

// Result: LastName, FirstName, MiddleName
CONCAT_WS( ', ', LastName, FirstName, MiddleName )

In cases where you want to use CONCAT_WS with different 'separators' just set the first input to an empty string:

// Result: LastName, FirstName (MiddleName)
CONCAT_WS( '', LastName, ', ', FirstName, ' (', MiddleName, ')' )

Answer 2

Then just use

CONCAT(LastName, ', ', FirstnName, ' ', MiddleName)

CONCAT_WS is used to concatenate with only one seperator while the CONCAT appends strings together in any way you want.