How the CONCAT() function works in Mariadb?

The CONCAT() function is a string function that concatenates two or more strings into one string. The function returns a string that is the result of joining the arguments in the order they appear.

Posted on

The CONCAT() function is a string function that concatenates two or more strings into one string. The function returns a string that is the result of joining the arguments in the order they appear. The function returns NULL if any argument is NULL.

Syntax

The syntax of the CONCAT() function is as follows:

CONCAT(string1, string2, ..., stringN)

The function takes at least one argument. The arguments are the strings that are concatenated. The function can handle binary or blob values as well as strings.

Examples

Example 1: Concatenating two strings

The following example uses the CONCAT() function to concatenate two strings.

SELECT CONCAT('Hello', 'World');

The output is:

+--------------------------+
| CONCAT('Hello', 'World') |
+--------------------------+
| HelloWorld               |
+--------------------------+

Example 2: Concatenating three strings

The following example uses the CONCAT() function to concatenate three strings.

SELECT CONCAT('This', 'is', 'a sentence.');

The output is:

+-------------------------------------+
| CONCAT('This', 'is', 'a sentence.') |
+-------------------------------------+
| Thisisa sentence.                   |
+-------------------------------------+

Example 3: Concatenating strings with a NULL value

The following example uses the CONCAT() function to concatenate two strings, one of which is NULL.

SELECT CONCAT('Hello', NULL);

The output is:

+-----------------------+
| CONCAT('Hello', NULL) |
+-----------------------+
| NULL                  |
+-----------------------+

Example 4: Concatenating strings with a binary or a blob value

The following example uses the CONCAT() function to concatenate two strings, one of which is a binary value.

SELECT CONCAT('Hello', 0x21);

The output is:

+-----------------------+
| CONCAT('Hello', 0x21) |
+-----------------------+
| Hello!                |
+-----------------------+

The binary value 0x21 is equivalent to the ASCII character ‘!’. Similarly, you can use a blob value as an argument.

Example 5: Concatenating strings with different character sets

The following example uses the CONCAT() function to concatenate two strings with different character sets. The first string is in utf8mb4 and the second string is in latin1.

SELECT CONCAT(_utf8mb4'你好', _latin1'World');

The output is:

+------------------------------------------+
| CONCAT(_utf8mb4'你好', _latin1'World')   |
+------------------------------------------+
| 你好World                                |
+------------------------------------------+

The function converts the second string to the character set of the first string before concatenating.

There are some other functions that are related to the CONCAT() function in Mariadb. They are:

  • CONCAT_WS(): This function concatenates two or more strings with a specified separator. The name of the function stands for concatenate with separator. The separator is added between the strings to be concatenated. The function skips the NULL values in the arguments list. If the separator is NULL, the function returns NULL.
  • CONCAT_WS_X(): This function is similar to the CONCAT_WS() function, except that it does not skip the NULL values in the arguments list. It treats them as empty strings.
  • GROUP_CONCAT(): This function concatenates values from a group of rows with a specified separator. It returns a string or a blob value.

Conclusion

The CONCAT() function is a simple function to join strings into one string. It can handle NULL values and binary or blob values as arguments. It is similar to the CONCAT_WS() function, but without a separator argument. It is also similar to the CONCAT_WS_X() function, but it returns NULL if any argument is NULL. The GROUP_CONCAT() function is another function that can concatenate values from a group of rows.