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, output from user defined functions or scalar sub queries etc.
SQL Server and Microsoft Access use the + operator. 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 / Microsoft Access
SELECT FirstName + ' ' + LastName As FullName FROM Customers
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.
SELECT FirstName || ' ' || LastName As FullName FROM Customers
MySQL uses the CONCAT(string1, string2, string3...) function. The above example would appear as follows in MySQL
SELECT CONCAT(FirstName, ' ', LastName) As FullName FROM Customers
In this article we have seen how to append strings to one another using string concatention functions provided in SQL. We hope you will find many uses for using these string functions in your databases.
Discuss this article in the forums