CAST versus CONVERT

Written 11/3/2017

Integers take precedence

If you want to concatenate nonstring values to strings, you have to convert the nonstring values into strings. You will find that string values, that can be implicit converted to an integer, will be converted to an integer, if you do not specifically tell, that you want to treat the value as a string. This happens, if you are in a context of integers. To illustrate this, you can run the following query:

select 40 + '2'

The result will be: 42

You have the number 40. It will take precedence as data type, and therefore the string '2' will be treated as an integer as well. Integers have higher precedence than strings, when determine the data type.

On the other hand, if you run:

select '40' + '2'

The result will be: 402

You are not in an integer context, so both values will be treated as strings.

Finally you can try to run:

select 40 + 'two'

This will result in the following error: Conversion failed when converting the varchar value 'two' to data type int.
It is because it is not allowed to concatenate a nonnumeric string, such as 'two', and a number, without converting the number to a string.

CAST

One way to cast a value to a specific data type, is using the CAST function.
CAST is compliant with the ANSI SQL-99 standard.

Syntax:

CAST(value AS new data type)

For example you can try the following query:

select CAST(40 AS nvarchar) + CAST(2 AS nvarchar)

The result will be 402, as both values are casted to strings, before concatenated.

CONVERT

Another way to cast a value to a specific data type, is using the CONVERT function.
CONVERT is NOT compliant with the ANSI SQL-99 standard.

Syntax:

CONVERT(new data type, value)

For example you can try the following query:

select CONVERT(nvarchar, 40) + CONVERT(nvarchar, 2)

The result will be 402, as both values are converted to strings, before concatenated.