Learn how to concatenate data in SQL Server
By: Daniel Calbimonte | Updated: 2022-08-12 | Comments (3) | Related: More > TSQL
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.
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.
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.
For more information, refer to these links:
- Concatenate SQL Server Columns into a String with CONCAT()
- New Data Type Conversion Functions in SQL Server 2012
- SQL Server CONCAT Function
- COALESCE SQL Function
- SQL Server String Functions
- SQL String functions in SQL Server, Oracle and PostgreSQL
- CONCAT and CONCAT_WS function in SQL Server
- SQL Server CONCAT_WS Function
- SQL Server SUBSTRING Function
- SQL Substring Function Examples with T-SQL, R and Python
About the author
View all my tips
Article Last Updated: 2022-08-12