6 Ways to Concatenate a String and a Number in SQL Server

If you’ve ever tried to concatenate a string with a number while using SQL Server, but received an error, this article should clear things up for you. There’s more than one way to perform concatenation using T-SQL in SQL Server, and if you’re concatenating different data types (like a string and a number) then you may receive an error, depending on how you do the concatenation.

The thing to remember when concatenating different data types is that they need to be converted into the same data type first. More specifically, when concatenating a string with a number, the number will need to be converted to a string before it can be concatenated with the string. Fortunately SQL Server/T-SQL makes this a breeze.

This article presents six ways to concatenate strings with numbers using T-SQL.

The CONCAT() Function

The most obvious (and possibly the best) way to concatenate a string and a number is to use the CONCAT() function. This allows you to provide the string and the number as two separate arguments. SQL Server will then concatenate them, and your concatenation is complete.

Example

SELECT CONCAT('Comments: ', 9) AS Result;

Result:

Result     
-----------
Comments: 9

As you can see, this works perfectly. The string and the number are presented as one field.

Note that you’re not limited to just one string and one number – the CONCAT() function can accept up to 254 arguments (i.e. you can join up to 254 strings/numbers together.

Also note that CONCAT() implicitly converts all arguments to string types before concatenation. Also, CONCAT() implicitly converts any null values to empty strings.

The CONCAT_WS() Function

We can take the previous example a step further by using the CONCAT_WS() function. This function allows you to specify a separator.

To provide a separator, simply add that as the first argument. Then specify the string and number as the second and third arguments.

SELECT CONCAT_WS(': ', 'Comments', 9) AS Result;

Result:

Result     
-----------
Comments: 9

Note that this function is not necessarily suitable for separating name/value pairs, as the separator is added between each individual item that’s concatenated (so it will also be added between each pair in the event you have multiple pairs). In our case it works fine, because we only have one name/value pair to concatenate.

However, you can always nest multiple CONCAT_WS() functions to return the correct concatenation for your needs (so that you can have a different separator between each pair).

The CONVERT() Function

If for some reason you can’t (or don’t want to) use the CONCAT() or CONCAT_WS() functions, you can perform a concatenation using the string concatenation operator (+). When you use this method, you’ll need to perform any data type conversions manually.

One way to convert between data types is to use the CONVERT() function.

Here’s how you’d do this using the same data from the previous examples:

SELECT 'Comments: ' + CONVERT(varchar(12), 9) AS Result;

Result:

Result     
-----------
Comments: 9

In this case I convert the integer to varchar(12), but this can be any data type and length you require.

The CAST() Function

The CAST() function is very similar to the CONVERT() function. The main difference is that CAST() is standard ANSI and CONVERT() is specific to T-SQL. Having said that, CONVERT() does have some extra functionality, but for the purposes of these examples, you could use either one.

Here’s the CAST() version using the same data from the previous examples:

SELECT 'Comments: ' + CAST(9 AS varchar(12)) AS Result;

Result:

Result     
-----------
Comments: 9

The TRY_CONVERT() Function

One potential issue when using the CONVERT() function is that, if the conversion doesn’t succeed, you’ll get an error. While this is fine in many cases, in some cases this might not be desirable. In such cases, you might find TRY_CONVERT() more suitable.

The TRY_CONVERT() function converts the data type just as the CONVERT() function does. However, if the data can’t be converted, then it will return null.

For example, if we try to do the following, we get an error:

SELECT 'Comments: ' + CONVERT(varchar(1), 10.00) AS Result;

Result:

Error: Arithmetic overflow error converting numeric to data type varchar.

However, if we use TRY_CONVERT(), we get null:

SELECT 'Comments: ' + TRY_CONVERT(varchar(1), 10.00) AS Result;

Result:

Result
------
null

You can use conditional programming in this case to perform a different task depending on the outcome.

The TRY_CAST() Function

The TRY_CAST() function works in the same way. It converts the data type just as the CAST() function does, and, if the data can’t be converted, then it will return null.

For example, this throws an error:

SELECT 'Comments: ' + CAST(10.00 AS varchar(1)) AS Result;

Result:

Error: Arithmetic overflow error converting numeric to data type varchar.

But if we use TRY_CAST() instead, we get null:

SELECT 'Comments: ' + TRY_CAST(10.00 AS varchar(1)) AS Result;

Result:

Result
------
null