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.
Well written and so easily understandable
with postgres, there is a difference between || and concat.
query:
SELECT CONCAT(‘John’, null, ‘Smith’), ‘John’ || null || ‘Smith’
result :
JohnSmith;null