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