SQL Concatenate – Combining Columns in a Table
We can combine multiple columns together into one in SQL using concatenation. Many databases have their own versions of combining columns. As a result, this makes it difficult to use one universal SQL function.
There isn’t a universal concatenation function so we can look at several of the largest SQL relational databases and identify examples in each one. The CONCAT() function is commonly used across SQL relational databases so it’s a safe bet to try that out first.
Amazon Redshift, Azure SQL DB, MySQL, Postgres DB & SQL Server Syntax
SELECT CONCAT(<column_1>, <column_2>, <column_n…>) AS <column_name> FROM <table>;
Amazon Redshift, Oracle DB , Postgres DB & SQLite Syntax
SELECT (<column_1> || <column_2> || <column_n…>) AS <column_name> FROM <table>;
SQL Server & Azure SQL DB Syntax
SELECT (<string_1> + <string_2> + <string_n…>) AS <column_name> FROM <table>;
Practical Examples of Concatenation
It’s important to ensure that we are combining like fields that are strings otherwise we may produce an error. Each database has it’s own list of compatible data types when trying to combine columns together. We can look at some practical examples to show us how to write out our SQL queries.
Trouble-shooting & Final Thoughts
There are several things to keep in my when using concatenation to combine multiple columns in SQL. As an example, MySQL can actually support the double pipe operator, but it must be enabled. Since this is the case, it’s easier to just use the already enabled concatenation features. However, we may want to enable this feature if we’re migrating substantial code that already uses the || operation.
We also have to be careful when using the ‘+’ operator because it can only be used with strings. This is not to be confused with using ‘+’ as an arithmetic operation if we were using integers or decimals.
For the most part combining fields is straight-forward and shouldn’t cause much trouble. The most common issues will be using the wrong operator in the wrong database or using incompatible data types when combining columns.