SQL Concatenate: A How-To Guide with Examples

String concatenation is the process of joining two string values together. Let’s see how to do it in several different databases.

What is String Concatenation in SQL?

The definition of concatenate means to join two things together. String concatenation in SQL (and other programming languages) is the process of combining two strings together or appending one string to the end of another string.

A common example of this is combining a person’s first name and last name to get their full name.

String concatenation is done slightly differently in each database vendor (Oracle, SQL Server, MySQL, PostgreSQL, etc).

 

Concatenation In Each Database

This table shows how to concatenate in each database:

Database	Method
Oracle	|| or CONCAT()
SQL Server	+ or CONCAT()
MySQL	|| or CONCAT()
PostgreSQL	|| or CONCAT()

Let’s take a look at some examples.

 

Oracle Concatenation

To concatenate strings in Oracle SQL, you can use the || operator or the CONCAT function.

Here’s an example using pipes:

SELECT 'John ' || 'Smith' AS fullname
FROM dual;

Result:

FULLNAME

John Smith

Here’s an example using the CONCAT function:

SELECT CONCAT('John ', 'Smith') AS fullname
FROM dual;

Result:

FULLNAME

John Smith

Should you use the pipes or the CONCAT function in Oracle?

Personally, I prefer to use the pipes as I find it easier to read. But, more importantly, using pipes allows you to concatenate more than two strings:

SELECT 'John' || ' ' || 'Smith' AS fullname
FROM dual;

If you want to concatenate more than two strings in Oracle using CONCAT, you’ll need to nest the functions, which can get messy:

SELECT CONCAT(CONCAT('John', ' '), 'Smith') AS fullname
FROM dual;

 

SQL Server Concatenation

String concatenation in SQL Server is done using the + symbol or the CONCAT function.

Here’s an example using the + symbol:

SELECT 'John ' + 'Smith' AS fullname
FROM dual;

Result:

FULLNAME

John Smith

SQL Server does not support the || operator for concatenation.

Another way is using the CONCAT function. Similar to MySQL and PostgreSQL, you can use more than two string values if you need.

For example:

SELECT CONCAT('John ', 'Smith') AS fullname;

Result:

FULLNAME

John Smith

 

MySQL Concatenation

MySQL supports the CONCAT function:

SELECT CONCAT('John ', 'Smith') AS fullname;

Result:

FULLNAME

John Smith

The MySQL CONCAT function supports multiple parameters, so you don’t need to nest CONCAT functions like Oracle.

SELECT CONCAT('John', ' ', 'Smith') AS fullname;

Result:

FULLNAME

John Smith

MySQL also supports the double pipes ||, but you need to change either of these two settings first:

SET sql_mode='PIPES_AS_CONCAT';

SET sql_mode='ANSI';

 

PostgreSQL Concatenation

In PostgreSQL, you can use either the double pipes || or the CONCAT function, just like Oracle.

Here’s an example using pipes:

SELECT 'John ' || 'Smith' AS fullname;

Result:

FULLNAME

John Smith

Here’s an example using the CONCAT function:

SELECT CONCAT('John', ' ', 'Smith') AS fullname;

Result:

FULLNAME

John Smith

In PostgreSQL, the CONCAT function supports multiple parameters, unlike Oracle.

 

Conclusion

String concatenation in SQL is a common string manipulation technique. It’s done slightly differently in each database vendor.