Open Minded (7/1/2010)
I think MDAS is also followed when there are a bunch of operators between numbers. I guess 'select 6+2+-+7' became 1 happened in this manner:
- . 6+2+-+7 -> start
- 8-7 -> 7 and 2 are added to an implicit zero.
- 1 -> finally, subtraction
I think I could post a cute QOTD based on this, 'select 6+2+*-+7'?
My first guess is 1 if implicit zeros exists. Now let me start my 2005 and run the select...Hmm...Interesting. I had to do 'select 6+2+0*0-+7'. So implicit zeros do not exist, it's just a thing for add and sub operators.
Btw, I did not get the question right.
Hi Open Minded,
Your explanation of 6+2+-+7 is not correct, and the reason you got it wrong is the same reason why 6+2+*-+7 does not work.
There is no conversion of - to 0, as you suggest. There is a conversion of '-' to 0. The single quotes make a huge difference here.
With the parentheses, '-' is a string constant. And since 6, 2, and 7 are all integer constants, and the rules of data type precedence say that strings are converted to integer, not the other way around, SQL Server will convert the string constant '-' to an integer - which results in the value 0.
Without the parentheses, - is an operator. Depending on the context, SQL Server will interpret it as the binary subtraction operator, or the prefixed unary negative operator.
Your 6+2+*-+7 results in an error, but that is not because of the trailing *-+7 - the error is caused by the bit before that. Here is the simplest form to reproduce that same error: SELECT 1+*1. There is no unary operator *, and no postfixed unary operator +, so this expression is invalid. To prove that the trailing end of your expression does parse okay, run SELECT 6+2*-+7 (I removed the + between 2 and *) and get the result -8. bothe the - and the + before the 7 are interpreted as prefixed unary operators (for negative and positive), so the order of evaluation is 6+(2*(-(+7))) = 6 + (2*(-7)) = 6 + (-14) = -8.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis