Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 11 down vote accepted

How does '+' operator behave in following statement?

It is parsed as a unary plus, and ignored, as described in this Connect item. While not strictly correct, the behaviour will not be changed because it would "introduce unnecessary changes for application code" and has "backward compatibility implications".


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

share|improve this answer

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.

share|improve this answer
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 '14 at 12:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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