Learn how to concatenate data in SQL Server

By:   |   Updated: 2022-08-12   |   Comments (3)   |   Related: More > TSQL


Problem

There is often the need to concatenate data in Microsoft SQL Server in SQL queries or stored procedures to make one long string instead of having separate columns. In this SQL tutorial, we will show several ways that this can be done with the Transact-SQL (T-SQL) language.

Solution

We will cover various ways to concatenate SQL data in SQL Server and some of the things you need to be aware of when doing this.

String Concatenate data with + operator

The + concatenation operator is the most common way to concatenate strings in T-SQL. The following example concatenates the FirstName and LastName with a space between them.  Here is the syntax with multiple string values:

SELECT FirstName + SPACE(1) + LastName as fullname
FROM [Person].[Person]
The + operator

The next example concatenates the FirstName, MiddleName, and LastName with spaces between each.  Here is the SQL syntax:

SELECT FirstName + SPACE(1) + MiddleName + SPACE(1) + LastName as fullname
FROM [Person].[Person]

Note the NULL values in the output below. This is because when you concatenate strings that have a NULL value, the entire result is NULL. So in this case we can pretty much determine this issue is related to missing MiddleName values since we didn't have this issue above.

the + operator with null values

If you run this query, you will notice that several MiddleName values are NULL.

SELECT TOP (20) 
       [FirstName]
      ,[MiddleName]
      ,[LastName]
  FROM [Person].[Person]
sample data of the person table

To fix this problem, we need to convert the NULL value to an empty value using the COALESCE function like this.

SELECT FirstName + SPACE(1) + COALESCE(MiddleName,'') + SPACE(1) + LastName as fullname
FROM [Person].[Person]

As you can see, the values are now fine, but we do get an extra space for names that do not have a MiddleName. Also, this same approach would work if the FirstName or LastName had NULL values.

COALESCE example

Let's take a look at another example. We will use an integer value and concatenate it with a string.

Here is the data.

SELECT [ExpYear]
FROM [Sales].[CreditCard]
expyear data

In the following example, we will try to concatenate the expYear with a string.

SELECT 'The expiration date is:' + SPACE(1) + [ExpYear] as results
FROM [Sales].[CreditCard]

We get this error message because the concatenation is trying to convert the string to smallint and it fails.

Msg 245, Level 16, State 1, Line 29
Conversion failed when converting the varchar value 'The expiration date is: ' to data type smallint.

To fix this problem, we need to convert the ExpYear to varchar as follows.

SELECT 'The expiration date is:' + SPACE(1) + CAST([ExpYear] as varchar(4)) as results
FROM [Sales].[CreditCard]

The results are the following:

concatenate string with numbers

The CAST function converts the integer into a string. Optionally, we can use TRY_CAST with the same result.

SELECT 'The expiration date is:' + SPACE(1) + TRY_CAST([ExpYear] as varchar(4)) as results
FROM [Sales].[CreditCard]

The main difference between CAST and TRY_CAST is that TRY_CAST handles errors. If an error occurs when using the function, the TRY_CAST will show a NULL value while the CAST displays an error message.

Another option is to use CONVERT to convert the integer to a string as shown below.

SELECT 'The expiration date is:' + SPACE(1) + CONVERT(varchar(4),[ExpYear]) as results
FROM [Sales].[CreditCard]

Additionally, you can use the TRY_CONVERT which is similar to TRY_CAST.

SELECT 'The expiration date is:' + SPACE(1) + TRY_CONVERT(varchar(4),[ExpYear]) as results
FROM [Sales].[CreditCard]

As we can see, there are two problems when using the + operator:

  • When we have NULL values, we need to convert the NULL value to some value.
  • When we have different data types to concatenate, we need to convert the values to strings using functions like CAST, CONVERT, TRY_CAST, and TRY_CONVERT.

Concatenate data with += operator

We can also use the += operator to concatenate a previous value with a new value.

DECLARE @msj varchar(30) = CONCAT('hello', SPACE(1))

SET @msj += 'MSSQLTIPS'

SELECT @msj as mymsj
the += operator

So, in this example, we have the @msj variable with "hello" and a space. If you add a value, we do not need to do @msj = @msj + 'MSSQLTIPS' the += will do the job. This tip is frequently used by programmers, but not all DBAs know this trick.

String Concatenate data with SQL CONCAT function

We can use CONCAT SQL command to concatenate values (or parameters) separated by commas.

SELECT CONCAT(FirstName, SPACE(1), LastName) as fullname
FROM [Person].[Person]
concatenate function

Now, let's see what happens if we use the MiddleName that has NULL values.

SELECT CONCAT (FirstName, SPACE(1) ,MiddleName, SPACE(1), LastName) as fullname
FROM [Person].[Person]

As you can see, we still get good output even without having to first handle the NULL values.

concat with null values

What about numeric values? Can we easily concatenate numbers and strings? Let's try.

SELECT CONCAT('The expiration date is:', SPACE(1), [ExpYear]) as results
FROM [Sales].[CreditCard]

Here are the results as a single string.

concat string with numbers

As you can see, concatenation is much easier using the CONCAT function in a SQL database.

Next Steps

For more information, refer to these links:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips


Article Last Updated: 2022-08-12

Comments For This Article




Friday, August 12, 2022 - 2:38:41 PM - Walter Pelowski Back To Top (90377)
I surprised you don't mention the CONCAT_WS function in this article. It has the benefit of not having extra spaces or having to do a COALESCE in the middle.
https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver15

From the documentation...
CONCAT_WS ignores null values during concatenation, and does not add the separator between null values. Therefore, CONCAT_WS can cleanly handle concatenation of strings that might have "blank" values - for example, a second address field.

Friday, August 12, 2022 - 11:58:53 AM - Karel Back To Top (90375)
Hi,

Nice post.
To avoid repeating spaces when there is a null value, you can use
SELECT [FirstName] +SPACE(1) + Coalesce([MiddleName] + SPACE(1) , '') + [LastName] FROM [dbo].[Person]
There will only be a space added when there is a value for middlename.
It's a minor detail, but it makes the output a little bit nicer (for reporting for example)

Friday, August 12, 2022 - 2:08:48 AM - Aldopaolo Palareti Back To Top (90374)
In the second example try
SELECT FirstName + COALESCE(SPACE(1) + MiddleName,'') + SPACE(1) + LastName as fullname
FROM [Person].[Person]
to delete the extra space














get free sql tips
agree to terms