SQL Book

SQL CONCATENATE (appending strings to one another)

String concatenation means to append one string to the end of another string. SQL allows us to concatenate strings but the syntax varies according to which database system you are using. Concatenation can be used to join strings from different sources including column values, literal strings, the output from user-defined functions or scalar sub-queries, etc.

What is the concatenation operator in SQL?

The operator that is used to concatenate strings in SQL depends on what database you are using.

SQL Server and Microsoft Access

SQL Server and Microsoft Access use the + operator.


-- SQL Server / Microsoft Access
SELECT 'Happy' + ' ' + 'Birthday' As BirthdayGreeting

Oracle

Oracle uses the CONCAT(string1, string2) function or the || operator. The Oracle CONCAT function can only take two strings so the above example would not be possible as there are three strings to be joined (FirstName, ' ' and LastName). To achieve this in Oracle we would need to use the || operator which is equivalent to the + string concatenation operator in SQL Server / Access.


-- Oracle
SELECT 'Happy' || ' ' || 'Birthday' As BirthdayGreeting

MySQL

MySQL uses the CONCAT(string1, string2, string3...) function. The above example would appear as follows in MySQL


-- MySQL
SELECT CONCAT('Happy', ' ', 'Birthday') As BirthdayGreeting

MySQL can also be set to use the || operator for string concatenation. However, be aware if you set this then MySQL will treat || as a string concatenation operator rather than as a synonym for OR. This may affect other queries you may have already written that use ||. Here is an example of how to set the PIPES_AS_CONCAT option on @@SQL_mode.


-- set SQL_MODE to include PIPES_AS_CONCAT
SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',PIPES_AS_CONCAT');

-- to remove PIPES_AS_CONCAT from SQL_MODE
SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'PIPES_AS_CONCAT', '');

How do I join two columns in SQL

The following examples show how to use the column names and the SQL concatenation operator for SQL Server and Microsoft Access, MySQL and Oracle.

Assuming a table called Customer that has columns called FirstName and LastName, The example below appends the value in the FirstName column with ' ' and then appends the value from the LastName column to this. The resulting string is given an Alias of FullName so we can easily identify it in our resultset..


-- SQL Server and Microsoft Access
SELECT FirstName + ' ' + LastName FROM Customer AS FullName

-- Oracle
SELECT FirstName || ' ' || LastName FROM Customer AS FullName

-- MySQL
SELECT CONCAT(FirstName, ' ', LastName) FROM Customer AS FullName

-- MySQL when @@SQL_MODE includes PIPES_AS_CONCAT 
SELECT FirstName || ' ' || LastName FROM Customer AS FullName

Conclusion

In this article, we have seen how to append strings to one another using string concatenation functions provided in SQL. We hope you will find many uses for using these string functions in your databases.

Join our newsletter

Subscribe to our free weekly newsletter and get exclusive SQL tips and articles straight to your inbox. 

 Your email address is only used to send you our newsletter.