SQL Server: +(unary) operator on non-numeric Strings

by

Here is my own answer to this question (Please also see the update at the end):

No, there isn’t such unary operator defined on the String expressions. It is possible that this is a bug.

Explanation:

The given statement is valid and it generates the below result:

(No column name) 
----------------
ABCDEF
(1 row(s) affected)

which is equivalent to doing the SELECT statement without using the + sign:

SELECT  'ABCDEF'

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

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:

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)

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'`

or even this:

`SELECT  -'12345'`

generate the below error:

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?

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'

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!

UPDATE

Here it says this has been a bug in earlier versions but it won’t be fixed because of backward compatibility:

After some investigation, this behavior is by design since + is an unary operator. So the parser accepts “+ , and the ‘+’ is simply ignored in this case.
Changing this behavior has lot of backward compatibility implications so we don’t intend to change it & the fix will introduce unnecessary changes for application code.

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. CROSS JOIN vs INNER JOIN in SQL
  3. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  4. SET versus SELECT when assigning variables?
  5. Multiple INSERT statements vs. single INSERT with multiple VALUES
  6. Split function equivalent in T-SQL?
  7. Generate Dates between date ranges
  8. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  9. Where value in column containing comma delimited values
  10. Default row order in SELECT query – SQL Server 2008 vs SQL 2012
  11. SQL query to split column data into rows
  12. Common Table Expression, why semicolon?
  13. T-SQL split string based on delimiter
  14. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  15. Splitting delimited values in a SQL column into multiple rows
  16. Scope of temporary tables in SQL Server
  17. Sequence vs identity
  18. SQL “between” not inclusive
  19. Can I set ignore_dup_key on for a primary key?
  20. Using PIVOT in SQL Server 2008
  21. Stored procedure that exports data into csv files only exports to one file
  22. How do I prevent a database trigger from recursing?
  23. Convert Date format into DD/MMM/YYYY format in SQL Server
  24. How to combine results of two queries into a single dataset
  25. When using GETDATE() in many places, is it better to use a variable?
  26. Executing SQL Server Agent Job from a stored procedure and returning job result
  27. SQL take just the numeric values from a varchar
  28. Cumulative Total in MS Sql server [duplicate]
  29. SQL Server SELECT INTO @variable?
  30. How to subtract 30 days from the current date using SQL Server

Leave a Comment