How to combine two columns into a new column in SQL

By: Chad Churchwell | Updated: 2021-07-21 | Comments (17) | Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Functions System

Problem

I need to produce mailing labels from my Microsoft SQL Server database so I am using the + sign to concatenate the first, middle, and last names together. The issue I see is I get NULL for a lot of rows. This makes me unable to produce the full names. What are some options to address this problem? Check out this tutorial to learn more about concatenating data in SQL Server with T-SQL string concatenation.

Solution

Prior to SQL Server 2012 concatenation was accomplished by using the plus (+) sign to concatenate fields together of various data types (varchar, char, int, numeric, etc.). The limitation of this method is if any of the fields you are concatenating are NULL, the final string value is NULL. In SQL Server 2012 and later there is the CONCAT() function that replaces NULL with an empty string. Take a look at this tip to see how this new function works and how it can be beneficial in your code.

For this demo I am going to use the Person.Person table from the AdventureWorks2012 database to demo the SQL functions to generate a full name for creating mailing labels. First, the following example is the old technique to concatenate strings using the + sign (concatenation operator):

SELECT Title, FirstName, MiddleName, LastName, Title + ' ' + FirstName + ' ' + MiddleName + ' ' + LastName as MailingName FROM Person.Person

As you can see in the screen shot below the MailingName is NULL for any row that has NULL for any one of the name columns. The only rows that have MailingName filled in have a value for all the title, firstname, middlename, and lastname columns. This could be corrected by wrapping ISNULL(column,'') around all the columns in the concatenated field to account for any values having nulls, but that code gets long, messy, and hard to read.

Below is example syntax is using ISNULL along with the plus sign to concatenate values. The ISNULL function will replace NULL values with the value noted in the second parameter, which in this example is an empty string.

SELECT Title, FirstName, MiddleName, LastName, ISNULL(Title,'') + ' ' + ISNULL(FirstName,'') + ' ' + ISNULL(MiddleName,'') + ' ' + ISNULL(LastName,'') as MailingName FROM Person.Person

As you can see in the example below, the MailingName is no longer NULL as it replaced the NULL values with an empty string. This achieves the same as using the CONCAT() function, but requires a lot more code and readability.

The next set of code is using the new CONCAT() function that is in SQL Server 2012 and later versions with a SELECT statement. It replaces NULL values with an empty string of type VARCHAR(1). This SQL statement is much easier to read and write when you need to have NULL code handling in place and generate a single string in a single column with spaces as a separator.

SELECT Title, FirstName, MiddleName, LastName, CONCAT(Title,' ',FirstName,' ',MiddleName,' ',LastName) as MailingName FROM Person.Person

If you see the results of this, all MailingName values are present, even if they have some of the columns set to NULL.

As you can see this new function is very handy and behaves much different that the old form of concatenation. Instead of evaluating to NULL if any if the columns contain NULL values, the CONCAT() function replaces it with an empty string. This is very useful for coding around NULL values.

Next Steps

Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

View all my tips

Article Last Updated: 2021-07-21

I think I am doing about the same thing... combining two columns into one, creating extra records. Is there an easier way to do it than union?

this works, but it makes me list all fields twice; SELECT user_login AS user_idee, employee FROM employee, grpmebrs WHERE USER_ID = user_login UNION SELECT secgroup AS user_idee, employee FROM employee, grpmebrs

WHERE USER_ID = user_login

this concats them; SELECT (user_login||secgroup) AS user_idee, employee FROM employee, grpmebrs

WHERE USER_ID = user_login

any ideas?
THANKS!

To merge two columns value as one, we can concatenate it as one and use alias for that value. This is the simplest way to do it.

SELECT FirstName + ' ' + LastName as FullName FROM PersonalDetails

Here the combination of FirstName and LastName is separated by a blank space and given as FullName.

Views: 23432 | Post Order: 56

Report Error

How can we add a new column that depends on other columns in MySQL?

Suppose the current table in MySQL contains the following fields.

firstName	lastName
Will	Smith
Chris	Rock

We want to create a new column fullName that contains both fields.

firstName	lastName	fullName
Will	Smith	Will Smith
Chris	Rock	Chris Rock

This concatenation can be done through a simple CONCAT operation.

SELECT CONCAT(firstName, ' ', lastName) AS fullName FROM table_name;

Let’s update our table to include this new full name field.

We can use generated column expressions to compute column values based on other columns in the table (even other generated columns that are defined earlier in the table definition).

Let’s add a new generated column.

ALTER TABLE table_name ADD COLUMN fullName VARCHAR(100) AS (CONCAT(firstName, ' ', lastName));

AS indicates that the column is generated and will define the expression used to compute the column value.

We can also append the VIRTUAL or STORED keyword to this ALTER TABLE statement.

VIRTUAL (default): column values are not stored (require no storage) but are evaluated when rows are read
STORED: column values are evaluated and stored when rows are inserted or updated (can also be indexed)

To store the fullName field in our database, we’ll need to append STORED.

ALTER TABLE table_name ADD COLUMN fullName VARCHAR(100) AS (CONCAT(firstName, ' ', lastName)) STORED;

2.1. Create the new column

First, we want to add a new nullable column.

ALTER TABLE table_name ADD COLUMN fullName VARCHAR(100);

2.2. Populate the new column

Then, we want to populate this new column with the correct values.

UPDATE table_name SET fullName = CONCAT(firstName, ' ', lastName);

Finally, we need to ensure all new inserts and updates of the old column update the new column.

We can add a trigger for all INSERT and UPDATE statements on this table.

Whenever a new row is inserted or updated on table_name, we will create or update the fullName column with the correct, concatenated value.

CREATE TRIGGER insert_trigger BEFORE INSERT ON table_name FOR EACH ROW SET new.fullName = CONCAT(firstName, ' ', lastName); CREATE TRIGGER update_trigger BEFORE UPDATE ON table_name FOR EACH ROW SET new.fullName = CONCAT(firstName, ' ', lastName);