What to use, concat, concat_ws? in mysql

Question

In PHP, I need to change this data

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

To the line "Smith, Robert Thomas"

I'm not sure if I need to use 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 tips?

Answer 1

I understand that this is necro, but the following is worth noting:

Both CONCAT and CONCAT_WS are pretty much equivalent.

However, the most noteworthy difference is that CONCAT may not return the expected results for cases when any of the inputs is NULL. In these cases, CONCAT will return NULL, while 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 probably want to use CONCAT_WS .

The following equivalents, unless 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 delimiters, just set the first entry to an empty line:

 // 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 delimiter, while CONCAT concatenates strings together in any way.