16th Feb 2023 8 minutes read How to Concatenate Two Columns in SQL – A Detailed Guide Alexandre Bruffa sql concatenation In SQL, concatenation is the operation of joining together strings, or pieces of text information. Learn how to concatenate two columns in SQL with this detailed guide. SQL concatenation is the process of combining two or more character strings, columns, or expressions into a single string. For example, the concatenation of 'Kate', ' ', and 'Smith' gives us 'Kate Smith'. Enhance your SQL skills with our interactive SQL Practice Set! This comprehensive course contains nearly 90 hands-on exercises, divided into five topic-based sections, including a section dedicated to SQL concatenation. It's an excellent way to refresh your SQL knowledge and build confidence in your skills with each exercise solved. Don't miss this opportunity to practice SQL, including SQL concatenation, in a structured and interactive way. SQL concatenation can be used in a variety of situations where it is necessary to combine multiple strings into a single string. Some common use cases include: Creating full names or other composite strings from multiple columns in a table – e.g. concatenating a user’s first and last names to create a full name. Creating custom labels or titles by concatenating multiple string values. Creating a unique identifier by concatenating multiple columns – e.g. a customer ID and an order number. Creating email addresses by concatenating a username (katesmith) and a domain name (learnSQL.com). These are just a few examples, but SQL concatenation can be used in many other situations where combining multiple strings is necessary. How to Concatenate Two Columns in SQL The syntax for SQL concatenation can vary depending on the specific SQL dialect being used. Here are a few examples of the most well-known techniques for SQL concatenation. The || Operator Standard SQL uses the || operator (as well as a few other options). Most SQL databases, with the notable exception of SQL Server, support this operator. The || operator takes two or more arguments and returns a single concatenated string. Usage Let’s imagine the following case. We have a table called users that stores user information: id first_name middle_name last_name age marital_status -------------------------------------------------------------- 1 Derek Brent Zoolander 35 S 2 Marty George McFly 20 S Let’s use the || operator to concatenate users’ first and last names and get a full name: SELECT first_name || ' ' || last_name AS full_name FROM users; The result: full_name --------------- Derek Zoolander Marty McFly In this example, the || operator takes the first_name and last_name columns from the users table and concatenates them together with a space in between, resulting in a full name for each user. The result is then aliased as full_name and returned in the query results. You can concatenate multiple strings using the || operator by providing more than two arguments. For example, the following SQL statement concatenates users’ first, middle, and last names: SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name FROM users; The result: full_name --------------- Derek Brent Zoolander Marty George McFly The Concatenation Operator || Works with Non-Text Columns Too The two previous examples only contain strings; what about the other data types? The arguments of the || operator can be strings, text columns, expressions, and other data types like numbers or dates. They will be automatically converted to a string. Here is an example of how to use the || operator to concatenate the first name, the last name, and the age of a user to form a new string: SELECT first_name || ' ' || last_name || ': ' || age || ' yo' AS user_details FROM users; The result: user_details --------------- Derek Zoolander: 35 yo Marty McFly: 20 yo Note that the first argument of the concatenation must be of a text data type. If not, you need to cast it into a text type. Here is an example of how to use the || operator to concatenate the ID and the first name of a user to form a unique identifier: SELECT cast(id as VARCHAR) || '_' || first_name AS unique_id FROM users; The result: unique_id --------------- 1_Derek 2_Marty Beware of NULLs! The | | concatenation operator returns NULL for any NULL argument. Imagine that you have some empty or NULL values in your database: id first_name middle_name last_name age marital_status -------------------------------------------------------- 3 Terminator T-1000 1 S 4 Robocop 2 S If one of the arguments of the concatenation is NULL, the whole expression returns NULL. In the following example, we’ll try a concatenation with a NULL argument: SELECT first_name || ' ' || last_name AS full_name FROM users; The result: full_name --------------- Terminator T-1000 NULL The second result here is NULL: Robocop has a first name, but his middle and last names are NULL. If you concatenate 'Robocop' with NULL, you get NULL. Note: In the case of Oracle, a NULL string is an empty string. The concatenation “ignores” the NULL string, and the concatenated arguments are returned. Oracle will return the following: full_name --------------- Terminator T-1000 Robocop Use the COALESCE() Function to Concatenate NULL Values An incredible tip for avoiding NULL values is using the COALESCE() function. The COALESCE() function in SQL is a built-in function that returns the first non-NULL value in a list of expressions. The function takes one or more arguments and returns the first argument that is not NULL. In the following example, suppose that the last_name field could be NULL. We want to avoid a bad concatenation, so we use the COALESCE() function: SELECT first_name || ' ' || COALESCE(last_name, '') AS full_name FROM users; The result: full_name --------------- Terminator T-1000 Robocop Above, COALESCE() returns the last name if the last name is not NULL. If the last name is NULL, it returns the empty string ‘’. This allows us to avoid concatenating the NULL value of Robocop’s last name and getting a NULL result. The + Operator The + operator is used to concatenate strings in MS SQL Server. It takes two or more arguments and returns a single concatenated string. Here is an example of using the + operator to concatenate a user’s first and last names: SELECT first_name + ' ' + last_name AS full_name FROM users; The result: full_name --------------- Derek Zoolander Marty McFly With the + operator, none of the arguments will be converted automatically. Each argument of the expression needs to be of the VARCHAR type if the concatenation will be successful. To concatenate values of different types, we use the CAST() function: SELECT first_name + ' ' + last_name + ': ' + CAST(age AS VARCHAR) + ' yo' AS user_details FROM users; The result: user_details --------------- Derek Zoolander: 35 yo Marty McFly: 20 yo Without CAST(), SQL Server will throw the following error: MS SQL Token error: 'Error converting data type varchar to bigint.' The CONCAT Function CONCAT() is another standard SQL built-in function that concatenates two or more values. All popular database systems except SQLite accept this function; SQLite only accepts the || operator. The main differences between the || and + operators and the CONCAT() function are: CONCAT() arguments are automatically converted into strings, so you can use arguments of different data types. CONCAT() treats NULL values as empty strings and ignores them. The exception here is MySQL (and, to a lesser extent, MariaDB). In MySQL, the CONCAT() function is equivalent to the || operator; NULL arguments yield NULL results. Here is an example of using CONCAT() to concatenate users’ first and last names: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users; The result: full_name --------------- Derek Zoolander Marty McFly Terminator T-1000 Robocop In the following example, we’ll use CONCAT() to concatenate users’ ID and first name to form a unique identifier. Note that the CAST() function is not required: SELECT CONCAT(id, '_', first_name) AS unique_id FROM users; The result: unique_id --------------- 1_Derek 2_Marty 3_Terminator 4_Robocop The CONCAT_WS Function The CONCAT_WS function in SQL is similar to the CONCAT function, but it is used to concatenate two or more strings together with a separator. The function takes two arguments: the first argument is the separator, and the rest of the arguments are the strings you want to concatenate. All popular databases except Oracle and SQLite accept the CONCAT_WS function. This function ignores NULLs in arguments (other than the separator) – even for MySQL Let’s use the CONCAT_WS function to concatenate first_name and last_name values with a space separator: SELECT CONCAT_WS(' ', first_name, last_name) AS full_name FROM users; The result: full_name --------------- Derek Zoolander Marty McFly Terminator T-1000 Robocop Finally, let’s try concatenating three arguments, separated by spaces: SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name FROM users; The result: full_name --------------- Derek Brent Zoolander Marty George McFly Terminator T-1000 Robocop If you want to know more stunning SQL functions, I highly recommend reading the article 5 SQL Functions for Manipulating Strings by Marek Pankowski. Concatenating Two Columns In SQL Is Easy! I hope you enjoyed reading this article as much as I did writing it! The differences between SQL dialects are subtle, and it’s always exciting knowing more about them. Did this article encourage you to learn more about SQL? Don’t hesitate! Your SQL journey is already off to a good start, and LearnSQL.com has all the resources and help you need to become an SQL expert. Mastering SQL is a hands-on experience — learning the concepts is one thing, but writing good queries requires practice. I sincerely recommend our SQL Practice Set; it tests your basic SQL knowledge and shows you where you need to improve. So, what are you waiting for? Don’t just stick with concatenating columns – learn more SQL today! Tags: sql concatenation