Operator Coercion Rules
The implicit coercion rules for operators are as follows:
1. Generally, where a numeric type is required, a string type can be supplied. This is the case for c, text, char, varchar, nchar, and nvarchar. If the character data turns out not to be numeric in form, a conversion error will occur.
2. Generally, where a string type is required, a numeric type can be supplied. This is the case for all integers, decimal, and all floats.
3. When an operator combines two values of the same data class, string or number, then the following precedence, shown from highest to lowest, is followed:
nchar
nvarchar
c
text
money
char
varchar
float
decimal
int
The resolver coerces to types that retain data without over inflating it. For example, coercion of nchar to char is avoided due to loss of richness of data form or collation. Similarly, coercing to a long type might work but would be grossly inefficient. Associated with this is the practical notion that most operators have a type themselves, some are arithmetic (*, /, and so on) and some are string (||, LIKE, and so on).
4. The exception to Rule 3 is the "+" operator, which is an arithmetic operator as well as a concatenation operator for strings. The two modes coexist, but if a number is added to a string, then the result type is a number.
5. Mixed type comparisons between character and numeric data are virtually coerced into Numeric String data before being compared.