CONCAT function concatenates 2 or more strings into one string.
Syntax | CONCAT(string1, string2, …) |
Quick Example | SELECT CONCAT('A','B'); |
Null | If any value is NULL, the result is NULL |
Last Update: MySQL 5.6
Related functionality for CONCAT in MySQL:
CONCAT_WS(sep, s1, s2, …) | Allows to specify a separator | NULL is '' (empty string) |
|| Operator | Enabled if sql_mode PIPES_AS_CONCAT or ANSI is set Disabled by default, and || is a synonym for OR operator | If any value is NULL, the result is NULL |
GROUP_CONCAT | Concatenates values within a group defined by GROUP BY | NULL values skipped |
CONVERT function concatenates 2 or more strings:
SELECT CONCAT('AB', 'CD', 'EF'); -- Result: ABCDEF
If Any Value is NULL, the Result is NULL
Take this into account when you concatenate string literals with column values that may contain NULL:
SELECT CONCAT('AB', NULL, 'EF'); -- Result: NULL
You can use IFNULL function to replace NULL values before concatenation:
SELECT name, CONCAT('Price is ', IFNULL(price, 'N/A')) FROM prices;
Also CONCAT_WS function treats NULL as '' (empty string), and allows using '' as a separator:
SELECT CONCAT_WS('', 'AB', NULL, 'CD'); -- Result: ABCD
|| Operator to Concatenate Strings
Setting sql_mode to PIPES_AS_CONCAT or ANSI enables || to concatenate strings:
-- By default, || is synonym to OR logical operator SELECT 'A' || 'B'; -- Result: 0 SET sql_mode = PIPES_AS_CONCAT; -- Now we can use || to concatenate strings SELECT 'A' || 'B' || 'C'; -- Result: ABC -- If any value is NULL, the result is NULL SELECT 'A' || NULL || 'C'; -- Result: NULL
String concatenation in other databases:
Oracle:
|| Operator | NULL is '' (empty string) | |
CONCAT(s1, s2) | 2 arguments only | NULL is '' |
SQL Server:
+ Operator | If any value is NULL, the result is NULL, unless CONCAT_NULL_YIELDS_NULL is OFF | |
+= Operator | Transact/SQL only | s1 += s2 is equal to s1 = s1 + s2 |
PostgreSQL:
|| Operator | If any value is NULL, the result is NULL | |
CONCAT(s1, s2, …) | NULL is '' | |
CONCAT_WS(sep, s1, s2, …) | Allows to specify a separator | NULL is '' |
Concatenate strings:
Oracle:
Oracle supports CONCAT function, but it can accept only 2 arguments. For more than 2 arguments the conversion to || string concatenation operator or nested CONCAT calls is required:
-- Concatenate 2 strings SELECT CONCAT('AB', 'CD'); -- Concatenate more than 2 strings SELECT 'AB' || 'CD' || 'EF'; -- or SELECT CONCAT(CONCAT('AB', 'CD'),'EF') FROM dual;
SQL Server:
SQL Server does not support CONCAT function, so the conversion to + string concatenation operator is required:
-- Concatenate 2 strings SELECT 'AB' + 'CD'; -- Concatenate more than 2 strings SELECT 'AB' + 'CD' + 'EF';
PostgreSQL:
PostgreSQL supports CONCAT function with the same syntax, so no any conversion is required:
-- Concatenate 2 strings SELECT CONCAT('AB', 'CD'); -- Concatenate more than 2 strings SELECT CONCAT('AB', 'CD', 'EF');