SQL Concatenate Function

The Concatenate function combines multiple character strings together. Each database provides its own way(s) to do this:

• MySQL: CONCAT( )
• Oracle: CONCAT( ), ||
• SQL Server: +

Syntax

The syntax for CONCAT( ) is as follows:

CONCAT (str1, str2, str3, ...)

The above syntax concatenates str1, str2, str3, and any other strings together. Each str can be a column name, or it can be a literal character string (meaning a sequence of characters enclosed by two single quotes), or just white space.

Please note the Oracle CONCAT( ) function only allows two arguments -- only two strings can be put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using '||'.

The syntax for using '||' to concatenate is as follows:

str1 || str2 || str3 ...

The syntax for using '+' to concatenate is as follows:

str1 + str2 + str3 ...

Examples

We use the following table for our examples.

Table Geography

Example 1: Use CONCAT function to concatenate

MySQL/Oracle:

SELECT CONCAT(Region_Name, Store_Name) FROM Geography 
WHERE Store_Name = 'Boston';

Result:

Example 2: Use '||' to concatenate

Oracle:

SELECT Region_Name || ' ' || Store_Name FROM Geography
WHERE Store_Name = 'Boston';

Result:

Example 3: Use '+' to concatenate

SQL Server:

SELECT Region_Name + ' ' + Store_Name FROM Geography
WHERE Store_Name = 'Boston';

Result: