CONCAT
Description
The CONCAT function concatenates multiple strings in sequence into one string. This function supports a variable number of arguments and is one of the most basic and commonly used functions in string processing. It is widely used in scenarios such as data concatenation, report generation, and dynamic SQL construction. Note that if any argument is NULL, the entire result will be NULL.
Syntax
CONCAT(<expr> [, <expr> ...])
Parameters
| Parameter | Description |
|---|---|
<expr> | String expression to be concatenated, can be a string constant, column name, or other expression. Type: VARCHAR |
Return Value
Returns VARCHAR type, representing the concatenated string of all arguments.
Concatenation rules:
- Concatenates strings in argument order
- Supports any number of arguments (at least 1)
- Supports correct concatenation of UTF-8 multi-byte characters
- Numbers and other types are automatically converted to strings
Special cases:
- If any argument is NULL, returns NULL (this is the main difference from CONCAT_WS)
- If no arguments are provided, syntax error
- Empty string arguments do not affect the concatenation result
- Supports mixing with non-string types
Examples
- Basic string concatenation
SELECT CONCAT('a', 'b'), CONCAT('a', 'b', 'c');
+------------------+-----------------------+
| CONCAT('a', 'b') | CONCAT('a', 'b', 'c') |
+------------------+-----------------------+
| ab | abc |
+------------------+-----------------------+
- NULL value handling (key feature)
SELECT CONCAT('a', NULL, 'c'), CONCAT('hello', NULL);
+------------------------+---------------------+
| CONCAT('a', NULL, 'c') | CONCAT('hello', NULL) |
+------------------------+---------------------+
| NULL | NULL |
+------------------------+---------------------+
- Empty string handling
SELECT CONCAT('hello', '', 'world'), CONCAT('', 'test', '');
+-----------------------------+------------------------+
| CONCAT('hello', '', 'world') | CONCAT('', 'test', '') |
+-----------------------------+------------------------+
| helloworld | test |
+-----------------------------+------------------------+
- Mixed numbers and strings
SELECT CONCAT('User', 123), CONCAT('Price: $', 99.99);
+---------------------+---------------------------+
| CONCAT('User', 123) | CONCAT('Price: $', 99.99) |
+---------------------+---------------------------+
| User123 | Price: $99.99 |
+---------------------+---------------------------+
- Multiple argument concatenation
SELECT CONCAT('A', 'B', 'C', 'D', 'E'), CONCAT('1', '2', '3', '4', '5');
+----------------------------------+----------------------------------+
| CONCAT('A', 'B', 'C', 'D', 'E') | CONCAT('1', '2', '3', '4', '5') |
+----------------------------------+----------------------------------+
| ABCDE | 12345 |
+----------------------------------+----------------------------------+
- UTF-8 multi-byte character concatenation
SELECT CONCAT('ṭṛì', ' ', 'ḍḍumai'), CONCAT('Hello', ' ', 'ṭṛì', ' ', 'ḍḍumai');
+------------------------------+--------------------------------------+
| CONCAT('ṭṛì', ' ', 'ḍḍumai') | CONCAT('Hello', ' ', 'ṭṛì', ' ', 'ḍḍumai') |
+------------------------------+--------------------------------------+
| ṭṛì ḍḍumai | Hello ṭṛì ḍḍumai |
+------------------------------+--------------------------------------+
- Path and URL construction
SELECT CONCAT('/home/', 'user/', 'file.txt'), CONCAT('https://', 'www.example.com', '/api');
+--------------------------------------+----------------------------------------------+
| CONCAT('/home/', 'user/', 'file.txt') | CONCAT('https://', 'www.example.com', '/api') |
+--------------------------------------+----------------------------------------------+
| /home/user/file.txt | https://www.example.com/api |
+--------------------------------------+----------------------------------------------+
- Email address construction
SELECT CONCAT('user', '@', 'example.com'), CONCAT('admin.', 'support', '@', 'company.org');
+------------------------------------+-----------------------------------------------+
| CONCAT('user', '@', 'example.com') | CONCAT('admin.', 'support', '@', 'company.org') |
+------------------------------------+-----------------------------------------------+
| user@example.com | admin.support@company.org |
+------------------------------------+-----------------------------------------------+
