Strange CAST behaviour with INT and a single hyphe

问题:

I just answered this question: Concate Primary Keys in SQL

There I encountered a strange behaviour:

SELECT 5 + '-' +  8 

returns 13

SELECT CAST('-' AS INT) 

returns 0, which explains the above...

But: Why is a single hyphen casted to zero implictly?

Btw: It's the same with a single + or a (multi-)blank string...

This is related CAST hyphen (-) to Decimal but points to the fact, that a cast to decimal would not bring up these results...

回答1:

I'm writing this as an answer because it's too long/complex for a comment - but it's based off my comments. Also note - I DO NOT HAVE AN OFFICIAL SOURCE, so no confirmation that my logic is "what's implemented". (But it makes sense I think :))

But suppose you're writing a conversion function, that needs to perform.

So you have a string to validate - for example CAST('-50' AS INT);
Then take each character on its own:

`-` is a valid part of the conversion, so move to next character.  
`5` is a valid part of the conversion, so move to next character.  
`0` is a valid part of the conversion, so move to next character.  
Done.

So supposed the string CAST('-' AS INT);:

`-` is a valid part of the conversion, so move to next character.  
Done.

Now - you could do an additional check to invalidate because - is not allowed standalone, but that would require additional code.

Similar with + and spaces. As well as the same with currency and MONEY or period and money:

SELECT CAST('$' AS MONEY)
SELECT CAST('.' AS MONEY)

Both are valid characters - but in actuality only in connection with actual numbers as well. But they parse through - indicating that it's all intentional, and speed for conversion seems sensible.

回答2:

This has to do with negative and positive numbers. I got to this conclusion by checking ISNUMERIC:

SELECT ISNUMERIC('-12')  // Result: 1
SELECT ISNUMERIC('+12')  // Result: 1
SELECT ISNUMERIC('.12')  // Result: 1 (because "." can be cast to MONEY)

So in your case SELECT CAST('-' AS INT) would get a "negative 0". Even SELECT CAST('-.' AS MONEY) is legal.

回答3:

In the expression

5 + '-'

5 is a literal of type INT and '-' is a literal of type CHAR(1). + is going to be the numeric addition operator, and not the string concatenation operator, because implicitly converting CHAR to INT has higher priority than the other way around, which is unfortunate.

Converting '-' to INT yields 0, because - and + are legal as number signs, and a single sign without any digits is allowed. Why? Well, because they are. To my knowledge, this isn't documented anywhere. In fact, none of the rules are. CONVERT(MONEY, ',,,') yields 0 because , is ignored entirely as a digit separator, even though they're not separating any digits here. CONVERT(FLOAT, '+') is illegal, so is CONVERT(DECIMAL, '+'), but CONVERT(INT, '+') is just dandy. Books Online contains only the briefest references to behavior. If you reverse engineered the code implementing these behaviors, you'd no doubt find some ancient and dubious shortcuts in the parsers that are now maintained for compatibility.

Mind you, I'm pretty sure that if the conversion code were written today, Microsoft would take care that it does produce an error (like with FLOAT, DECIMAL or most any other numeric type), because the results are needlessly surprising. But I'm equally sure that the risk of breaking code in the wild that relies on this conversion working the way it does is far too great. (Imagine all the text files out there that use - as a "not applicable" value that currently gets imported as 0 and would now break with an error.)

回答4:

By default in such situations, SQL Server tries to cast to the type with the highest precedence among all types in the expression. Int has a higher precedence than char, so SQL Server cast '-' to int implicitly here. Here is a documentation about SQL Server types precedence.