MySQL / MariaDB Concatenate strings into one string - CONCAT() and CONCAT_WS() functions

Colleagues hello to all.

In today's article, we will look at how it is possible to combine rows into a single row in a MySQL and MariaDB database. It would seem that this is a fairly simple task, but not everything is so simple. Combining row fields into a single field in the result set is possible using several string manipulation functions. Two functions CONCAT() and CONCAT_WS() are responsible for concatenating rows in the MySQL and MariaDB database.

We will see how the functions work on the fio table that I created and added a few lines to it.

The CONCAT() function.

Function syntax:

SELECT CONCAT(string_1,string_2,...) FROM table_name WHERE field_name = value

A function can take one or more strings to concatenate.

In the first variant, we will try combine two fields, the user's first and last names.

sql> SELECT CONCAT(first_name, ' ' ,last_name) AS fio FROM fio WHERE id = '1';

As you can see, as a result, we combined two fields, but there is no separator between them.

In order to add a separator, you can add another line, but it will be as a space.

sql> SELECT CONCAT(first_name, ' ' ,last_name) AS fio FROM fio WHERE id = '1';

When concatenating strings using the CONCAT() function, one very important detail must be taken into account, if one of fields is NULL, then the function will return NULL.

sql> SELECT CONCAT(first_name, middle_name) AS fio FROM fio WHERE id = '5';

Function CONCAT_WS().

Function syntax:

SELECT CONCAT_WS(separator, string_1,string_2,...) FROM table_name WHERE field_name = value

The CONCAT_WS function differs from the CONCAT function in that CONCAT_WS takes a line separator as the first parameter, and only then the fields of the lines that we want to concatenate.

We will use the same examples as I showed above, only now instead of the CONCAT function we will use the CONCAT_WS function with a delimiter.

In the first variant, we will try combine two fields, the user's first and last names.

sql> SELECT CONCAT_WS('-',first_name, last_name) FROM fio WHERE id = 1;

As we remember from the example above, if NULL values ​​are passed to the CONCAT function, then the function itself will return NULL. With the  CONCAT_WS function, everything is a little different, if one of the fields contains NULL values, then the function will return the values ​​of that field that are not NULL.

sql> SELECT CONCAT_WS('-',first_name, middle_name) FROM fio WHERE id = 5;

Thank you all, I hope that my article helped you in some way.