What’s New ?

The Top 10 favtutor Features You Might Have Overlooked

Read More

Concatenate Two Columns in SQL (with Examples)

  • Feb 06, 2024
  • 8 Minutes Read
Concatenate Two Columns in SQL (with Examples)

The concatenation of columns is a fundamental operation in the programming world. It is very handy in designing a more comprehensive output table within an SQL database. In this article, we will dive deeper into concatenation in SQL for beginners, how to concatenate two columns, to uncover its features for creating more insightful databases.

What is Concatenation in SQL?

Concatenation refers to the process of combining two or more column values into a single new column. In SQL, concatenation is often done using the CONCAT function or the '+' operator, depending on the database system. Each method helps in merging column values, providing flexibility to the specific requirements of the database.

This function is particularly useful when creating a combined result from different individual columns of the table. For example, if your table has separate columns for country code and mobile number then you can concatenate them to full mobile numbers. 

Concatenation of columns enhances the SQL programming functionality, allowing developers and database administrators to optimize data processing and extraction. 

Concatenation in SQL using CONCAT Function

We can perform concatenation in SQL using the CONCAT function, which combines column values or other values from other tables with a space in between them. This is quite a simple operation that is often used to create combined values in the new column. These merged column values are often separated by a delimiter, such as a blank space.

Syntax:

SELECT
column1,
column2,
CONCAT(column1, ' ', column2) AS concatenated_column
FROM table_name;

 

Let us understand this through an example:

Table: phone_numbers

Concatentation SQL concat function phone number table

SQL Query:

SELECT
id,
first_name,
CONCAT(country_code, ' ', mobile_number) AS combined_phone_number
FROM
phone_numbers;

 

Output:

Concatentation SQL concat function phone number table Output

Concatenation using ‘+’ Operator

Concatenation in SQL is the result or overview of combining two or more columns with values into a single column. The concatenation result showcases the merged content of the specified columns and their values. To concatenate using this method we will use the “+” operator and blank space as a delimiter.

Syntax:

SELECT column,
column1 + ' ' + column2 AS concatenated_column
FROM table_name;

 

Let us understand this through an example:

Table: student_info

Concatentation SQL operator marks table

SQL Query:

SELECT
id,
first_name + ' ' + last_name AS full_name,
marks
FROM
student_info;

 

Output:

Concatentation SQL operator marks table output

The output here shows the concatenation of first name and last name to showcase the full name.

Handling NULL Values During Concatenation

Even during concatenation in SQL, we deal with NULL values, which is crucial to avoid erroneous results. When concatenating two or more columns that have null values, we can use the COALESCE function or the ISNULL function to manage such NULL cases.

The COALESCE function in SQL returns the non-null values among the other values. It reads multiple values and returns the value of the first non-null term from the left to the right.

Syntax:

SELECT column,
CONCAT(COALESCE(column1, ''), ' ', COALESCE(column2, ''))
AS concatenated_result
FROM table_name;

 

Let us understand this through an example:

Table: Employee

Concatentation SQL null values employees table

SQL Query:

SELECT
id,
COALESCE(first_name + ' ' + last_name, job_title) AS full_name_or_job_title
FROM
employee;

 

Output:

Concatentation SQL null values employees table output

COALESCE function is used here to concatenate first_name and last_name. If either of them is NULL, it displays the job_title as the alternative.

Using ISNULL Function

The ISNULL function is used to handle the NULL values within tables of your database. The primary feature of this function is to provide an alternative value to NULL values. It helps in dealing with unknown or missing data.

Syntax:

SELECT column,
column1 + ' ' + ISNULL(column2, '')
AS concatenated_result
FROM table_name;

 

Table: Office

Concatentation SQL isnull office table

SQL Query:

SELECT
id,
first_name,
last_name,
department
FROM
office
WHERE
department IS NULL;

 

Output:

Concatentation SQL isnull office table output

The output displays the values of the office employees where the department value is NULL.

Conclusion

In conclusion, concatenation in SQL is a fundamental and primary operation that allows developers to merge different columns into a combined column. It is a very handy tool for developers and database administrators, allowing them to structure and present data in ways that meet the requirements.

FavTutor - 24x7 Live Coding Help from Expert Tutors!

About The Author
Tanish Mallik
I am Tanish, a final-year computer science student with extensive expertise in SQL. My background in the field of data science has honed my strong analytical skills, and I also have hands-on experience in developing data integration and optimization pipelines using Python and MSBI. I am eager to contribute innovative ideas and make an impact in the ever-evolving world of technology.