If you get error Msg 245 that reads “Conversion failed when converting the varchar value…” when trying to perform a concatenation operation in SQL Server, it’s likely that you’re attempting to concatenate a string and a number.

Doing this will result in an error, due to SQL Server trying to add the string and number instead of concatenate them.

To fix this, either convert the number to a string, or use a function like CONCAT() or CONCAT_WS() to perform the concatenation.

The Error

Here’s an example of code that causes the error:

SELECT 'Player ' + 456;

Result:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Player ' to data type int.

SQL Server thinks that we’re trying to add the two operands, and so runs into a problem when trying to add the string and number.

Solution 1

One way to solve this problem is to explicitly convert the number to a string:

SELECT 'Player ' + CAST(456 AS varchar(3));

Result:

Player 456

This causes SQL Server to understand that we’re trying to concatenate the operands instead of add them.

Solution 2

Another way to do it is to use a function like CONCAT() to perform the concatenation:

SELECT CONCAT('Player ', 456);

Result:

Player 456

CONCAT() is a string function, and so SQL Server now understands that we want to concatenate two strings instead of add two numbers.

Alternatively, we could use the CONCAT_WS() function, which allows us to specify a separator to use between the concatenated arguments.