Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
1  
Concatenating nothing with something produces the same something. If you make a meal of just cheese and add nothing else to it, what do you have? The fact your empty hand moved towards the cheese doesn't change that. –  Phil Apr 29 at 1:06
    
Then how about select 'taco' +; ? Why is it not equivalent to + 'taco'? –  davisdba Apr 29 at 1:11
    
Taco with or without cheese? –  Phil Apr 29 at 1:13
    
Or cheese with or without taco, but where is the cheese? –  davisdba Apr 29 at 1:19
4  

2 Answers 2

up vote 3 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".

(Originally posted as a comment to 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  
That explains. The trailing white space is ignored, so cannot treated as empty string in the concatenate operator. That means select + 'taco' + + '' is a valid syntax too. Thanks. –  davisdba Apr 29 at 2:10
1  
I say the syntax is allowed because the parser's programmers were lazy. Another, even more silly example: SELECT 'tako'+++++++++++++++' with cheese'; –  ypercube Apr 29 at 9:04
    
-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 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.