SQL Server:非数字字符串上的 +(一元)运算符 [英] SQL Server: +(unary) operator on non-numeric Strings
问题描述
我很惊讶！以下语句在 SQL SERVER 中有效:
I am surprised! This statement below is valid in SQL SERVER:
SELECT +'ABCDEF'
SQL Server 是否已将 + 定义为字符串类型的 Unary 运算符?
Has SQL Server defined + as a Unary operator for string types?
推荐答案
这是我自己对这个问题的回答(另请参阅最后的更新):
Here is my own answer to this question (Please also see the update at the end):
不，String 表达式上没有定义这样的一元运算符.这可能是一个错误.
No, there isn't such unary operator defined on the String expressions. It is possible that this is a bug.
说明:
给定的语句是有效的，它生成以下结果:
The given statement is valid and it generates the below result:
(No column name)
----------------
ABCDEF
(1 row(s) affected)
这相当于在不使用 + 符号的情况下执行 SELECT 语句:
which is equivalent to doing the SELECT statement without using the + sign:
SELECT 'ABCDEF'
在编译时没有出现任何错误，实际上已成功执行，给人的印象是 + 在给定字符串上作为 Unary 操作进行操作.然而，在官方的T-SQL 文档中，并没有提到这样的操作符.实际上，在标题为字符串运算符"的部分中，+ 出现在 + (String Concatenation) 和 += (String Concatenation) 两个字符串操作中；但也不是 Unary 操作.此外，在标题为一元运算符"的部分中，三个运算符已被引入，其中只有一个是 + (Positive) 运算符.然而，对于这唯一一个似乎相关的操作，很快就会清楚，这个操作符也与非数字字符串值无关，因为 + (Positive) 操作符的解释明确指出此运算符仅适用于数值:"返回数值表达式 (一元运算符)".
Being compiled without giving any errors, in fact being executed successfully, gives the impression that + is operating as a Unary operation on the given string. However, in the official T-SQL documentation, there is no mentioning of such an operator. In fact, in the section entitled "String Operators", + appears in two String operations which are + (String Concatenation) and += (String Concatenation); but neither is a Unary operation. Also, in the section entitled "Unary Operators", three operators have been introduced, only one of them being the + (Positive) operator. However, for this only one that seems to be relevant, it soon becomes clear that this operator, too, has nothing to do with non-numeric string values as the explanation for + (Positive) operator explicitly states that this operator is applicable only for numeric values: "Returns the value of a numeric expression (a unary operator)".
也许，这个运算符是为了成功接受那些被成功评估为数字的字符串值，例如这里使用的那个:
Perhaps, this operator is there to successfully accept those string values that are successfully evaluated as numbers such as the one that has been used here:
SELECT +'12345'+1
当上面的语句被执行时，它会在输出中生成一个数字，它是给定字符串评估为数字和添加到它的数字值的总和，这里是 1 但它显然可以是任何其他数量:
When the above statement is executed, it generates a number in the output which is the sum of both the given string evaluated as a number and the numberic value added to it, which is 1 here but it could obviously be any other amount:
(No column name)
----------------
12346
(1 row(s) affected)
但是，我怀疑这种解释是否正确，因为它引发了以下问题:
However, I doubt this explanation is the correct as it raises to below questions:
首先，如果我们接受这个解释是正确的，那么我们可以得出结论，诸如 +'12345' 这样的表达式被计算为数字.如果是这样，那么为什么这些数字会出现在DATALENGTH、LEN等字符串相关的函数中.你可以看到这样的语句:
Firstly, if we accept that this explanation is true, then we can conclude that expressions such +'12345' are evaluated to numbers. If so, then why is it that these numbers can appear in the string related functions such as DATALENGTH, LEN, etc. You could see a statement such as this:
SELECT DATALENGTH(+'12345')
非常有效，结果如下:
is quite valid and it results the below:
(No column name)
----------------
5
(1 row(s) affected)
这意味着 +'12345' 被评估为字符串而不是数字.如何解释?
which means +'12345' is being evaluated as a string not a number. How this can be explained?
其次，类似的语句用-操作符，如:
Secondly, while similar statements with - operator, such as this:
`SELECT -'ABCDE'`
甚至这个:
`SELECT -'12345'`
产生以下错误:
Invalid operator for data type. Operator equals minus, type equals varchar.
为什么，当 + 运算符被错误地用于非数字字符串值时，它不应该在类似情况下产生错误吗?
Why, shouldn't it generate an error for similar cases when + operator has been wrongly used with a non-numeric string value?
因此，这两个问题使我无法接受以下解释，即这与数值文档中引入的 + (unary) 运算符相同.由于在其他任何地方都没有其他提及，可能是故意将其添加到语言中.可能是一个错误.
So, these two questions prevent me from accepting the explanation that this is the same + (unary) operator that has been introduced in the documentation for numeric values. As there is no other mentioning of it anywhere else, it could be that it is deliberately added to the language. May be a bug.
当我们看到这样的语句也没有产生错误时，问题看起来更严重:
The problem looks to be more severe when we see no error is generated for statements such as this one either:
SELECT ++++++++'ABCDE'
我不知道是否有任何其他编程语言接受这些类型的语句.但是如果有的话，很高兴知道他们将 + (unary) 应用于字符串的操作符用于什么目的.我无法想象任何用法！
I do not know if there are any other programming languages out there which accept these sort of statements. But if there are, it would be nice to know for what purpose(s) they use a + (unary) operator applied to a string. I cannot imagine any usage!
更新
这里说这是早期版本中的一个错误，但由于向后兼容而不会修复:
Here it says this has been a bug in earlier versions but it won't be fixed because of backward compatibility:
这篇关于SQL Server:非数字字符串上的 +(一元)运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
