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

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.

mysql_mariadb_concat_concat_ws

mysql_mariadb_concat_concat_ws


 

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';

mysql_mariadb_concat_concat_ws

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';

mysql_mariadb_concat_concat_ws

 

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';

mysql_mariadb_concat_concat_ws


 

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;

mysql_mariadb_concat_concat_ws

 

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;

mysql_mariadb_concat_concat_ws


 

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

 

SIMILAR ARTICLES

MySQL - Test Data Generation
MySQL - Test Data Generation
MYSQL
Read more
MySQL / MariaDB How to get the id of the last inserted record in a table - LAST_INSERT_ID
MySQL / MariaDB How to get the id of the last inserted record in a table - LAST_INSERT_ID
MYSQL
Read more
MySQL/MariaDB How to convert character case - UPPER and LOWER functions
MySQL/MariaDB How to convert character case - UPPER and LOWER functions
MYSQL
Read more