7

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?

0

2 Answers 2

13

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)

0

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.

1
  • 1
    -1 This answer is based on assumptions and is wrong. The comment of @Marin Smith (and so the community wiki answer of Paul White) gives the right answer to this question and proves it with a reference.
    – miracle173
    May 3, 2014 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.