SQL Concatenate Strings Example

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 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.

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

SELECT TOP (20) 
       [FirstName]
      ,[MiddleName]
      ,[LastName]
  FROM [Person].[Person]

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.

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]

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:

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

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]

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.

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.

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:

Resources

Community

Subscribe