MySQL - CONCAT Function - Concatenate Strings

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 in MySQL

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

MySQL CONCAT Function Details

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

MySQL CONCAT in Other Databases

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

MySQL CONCAT Function Conversion to Other Databases

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

Convert Online

Resources