'+' operator with one operand!

Question

How does '+' operator behave in following statement?

select + 'taco';  --Result is 'taco'

Is it doing string concatenation with first string blank ('' + 'taco'), or does it mean something else?

Answer 1

How does '+' operator behave in following statement?

It is parsed as a unary plus, and ignored.

The following was given in answer to Connect item 718176 on the subject (Connect has since been retired, and no archive of this page is available):

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.

---

_{(Community Wiki answer generated from a comment on the question by Martin Smith)}

Answer 2

The string concatenation operator "+", by definition, "concatenates two or more" strings. http://technet.microsoft.com/en-us/library/ms177561.aspx

This query executes successfully because the implied second string is the non-null concatenation identity (empty string).

This works in the same way as the operator in select +1; the implied second number is the non-null addition identity '0'.

The syntax is allowed because of the need for expressions such as select -1. Neither select 'taco' + nor select 1+ will execute, but only because of parser rules.

Consider the Execution Plans generated by these SQL Statements (I added top(1) to obtain a plan):

select top (1) + 'taco'

select top (1) '' + 'taco'

select top (1) 'taco'

The execution plans are identical. The "Compute Scalar" operator does not reveal any concatenation activity. The Query Optimizer simplifies the identity concatenation expression before execution.