June 30, 2010 at 10:34 am
Yes, it seems inconsistent that '-' will convert to int but not float. This comes out another way with the order of evaluation:
select 6 + '-' + 1.0;
select 6.0 + '-' + 1;
June 30, 2010 at 11:00 am
This sort of thing has tripped me up before. Sometimes it seems that things are implicitly cast in an unexpected order and can depend on the command/keyword being used. I wonder is there a more general rule as to when things might be implicitly cast?
Really interesting discussion. Thanks.
June 30, 2010 at 12:27 pm
June 30, 2010 at 4:40 pm
Learned something new AGAIN 😛 Now let's hope it sticks in my brain. Or at least the vague rememberance where I saw this first.
Got my missed point back, hehe 😀
June 30, 2010 at 11:18 pm
thanks... nice one
July 1, 2010 at 12:49 am
I agree with people saying that 5 would have been an interesting option! 🙂
July 1, 2010 at 12:57 am
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:
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.
July 1, 2010 at 1:50 am
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.
July 1, 2010 at 1:52 am
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.
You are mixing up two things. The implicit zero you mean is coming from a string converted to integer. Your select
select 6+2+*-+7
contains no strings.
You can actually easily see what SQL Server converts to what (for single characters only):
USE tempdb
GO
DROP PROCEDURE dbo.CheckConversion
GO
CREATE PROCEDURE dbo.CheckConversion AS
DECLARE @i int
SET @i = 0
WHILE @i <= 65535
BEGIN
BEGIN TRY
PRINT N'''' + NCHAR(@i) + N''' / NCHAR(' + CAST(@i as nvarchar(500)) + ') converts to ''' + CAST(CAST(NCHAR(@i) AS int) AS nvarchar(500)) + N''''
END TRY
BEGIN CATCH
-- PRINT ERROR_MESSAGE()
END CATCH
SET @i = @i + 1
END
GO
EXEC dbo.CheckConversion
' ' / NCHAR(32) converts to '0'
'+' / NCHAR(43) converts to '0'
'-' / NCHAR(45) converts to '0'
'0' / NCHAR(48) converts to '0'
'1' / NCHAR(49) converts to '1'
'2' / NCHAR(50) converts to '2'
'3' / NCHAR(51) converts to '3'
'4' / NCHAR(52) converts to '4'
'5' / NCHAR(53) converts to '5'
'6' / NCHAR(54) converts to '6'
'7' / NCHAR(55) converts to '7'
'8' / NCHAR(56) converts to '8'
'9' / NCHAR(57) converts to '9'
If you convert to money you get weird results btw:
'' / NCHAR(9) converts to '0.00'
'
' / NCHAR(10) converts to '0.00'
'' / NCHAR(11) converts to '0.00'
'' / NCHAR(12) converts to '0.00'
'
' / NCHAR(13) converts to '0.00'
' ' / NCHAR(32) converts to '0.00'
'$' / NCHAR(36) converts to '0.00'
'+' / NCHAR(43) converts to '0.00'
',' / NCHAR(44) converts to '0.00'
'-' / NCHAR(45) converts to '0.00'
'.' / NCHAR(46) converts to '0.00'
'0' / NCHAR(48) converts to '0.00'
'1' / NCHAR(49) converts to '1.00'
'2' / NCHAR(50) converts to '2.00'
'3' / NCHAR(51) converts to '3.00'
'4' / NCHAR(52) converts to '4.00'
'5' / NCHAR(53) converts to '5.00'
'6' / NCHAR(54) converts to '6.00'
'7' / NCHAR(55) converts to '7.00'
'8' / NCHAR(56) converts to '8.00'
'9' / NCHAR(57) converts to '9.00'
'\' / NCHAR(92) converts to '0.00'
' ' / NCHAR(160) converts to '0.00'
'¢' / NCHAR(162) converts to '0.00'
'£' / NCHAR(163) converts to '0.00'
'¤' / NCHAR(164) converts to '0.00'
'¥' / NCHAR(165) converts to '0.00'
'?' / NCHAR(2546) converts to '0.00'
'?' / NCHAR(2547) converts to '0.00'
'?' / NCHAR(3647) converts to '0.00'
'?' / NCHAR(5760) converts to '0.00'
'?' / NCHAR(6107) converts to '0.00'
' ' / NCHAR(8192) converts to '0.00'
' ' / NCHAR(8193) converts to '0.00'
' ' / NCHAR(8194) converts to '0.00'
' ' / NCHAR(8195) converts to '0.00'
' ' / NCHAR(8196) converts to '0.00'
' ' / NCHAR(8197) converts to '0.00'
' ' / NCHAR(8198) converts to '0.00'
'?' / NCHAR(8199) converts to '0.00'
'?' / NCHAR(8200) converts to '0.00'
'?' / NCHAR(8201) converts to '0.00'
'?' / NCHAR(8202) converts to '0.00'
'?' / NCHAR(8203) converts to '0.00'
'?' / NCHAR(8232) converts to '0.00'
'?' / NCHAR(8233) converts to '0.00'
'?' / NCHAR(8239) converts to '0.00'
'?' / NCHAR(8352) converts to '0.00'
'¢' / NCHAR(8353) converts to '0.00'
'?' / NCHAR(8354) converts to '0.00'
'?' / NCHAR(8355) converts to '0.00'
'£' / NCHAR(8356) converts to '0.00'
'?' / NCHAR(8357) converts to '0.00'
'?' / NCHAR(8358) converts to '0.00'
'P' / NCHAR(8359) converts to '0.00'
'?' / NCHAR(8360) converts to '0.00'
'?' / NCHAR(8361) converts to '0.00'
'?' / NCHAR(8362) converts to '0.00'
'?' / NCHAR(8363) converts to '0.00'
'€' / NCHAR(8364) converts to '0.00'
'?' / NCHAR(8365) converts to '0.00'
'?' / NCHAR(8366) converts to '0.00'
'?' / NCHAR(8367) converts to '0.00'
'?' / NCHAR(8368) converts to '0.00'
'?' / NCHAR(8369) converts to '0.00'
' ' / NCHAR(12288) converts to '0.00'
'?' / NCHAR(65020) converts to '0.00'
'?' / NCHAR(65129) converts to '0.00'
'$' / NCHAR(65284) converts to '0.00'
'?' / NCHAR(65504) converts to '0.00'
'?' / NCHAR(65505) converts to '0.00'
'?' / NCHAR(65509) converts to '0.00'
'?' / NCHAR(65510) converts to '0.00'
Best Regards,
Chris Büttner
July 1, 2010 at 2:13 am
@hugo and @christian. Excuse me if I was not clear, I was not thinking of the conversion of '-'. I was thinking of the precedence of operators.
For example, 'select 1 - 2' gives out '-1'. How about 'select 1 + 1 * 2'?
Also, 'select 1 +-+ 1 * 2' and 'select 1 +0+ 1 * 2' are different but 'select 1 ++ 1 * 2' is the same with the latter.
But I think you both pointed out when operators are alongside each other, they will be treated differently.
The only practical thing I can imagine of the above discussion is the building of a select where a user chooses the number and operations to perform but blank spaces from {edit: corrected 'for' to 'from'} unfilled slots/boxes may come in?
July 1, 2010 at 2:32 am
Hi Openminded,
I am not sure what your question is. But here is a transformation of the selects you gave which might be helpful to you.
Just remember that the unary plus (+) or minus (-) have a higher precedence than multiplication or division, which in turn also have a higher precedence than addition or substration.
Finally the calculation is executed from left to right (if not overridden by the precedence of operators or parentheses):
select 1 - 2
This is a simple substraction
select 1 + 1 * 2
This translates to 1 + (1 * 2)
select 1 +-+ 1 * 2
This translates to (1 + ((-(+1)) * 2))
select 1 +0+ 1 * 2
This translates to ((1 + 0) + (1 * 2))
select 1 ++ 1 * 2
This translates to (1 + ((+1) * 2))
Best Regards,
Chris Büttner
July 1, 2010 at 3:16 am
@christian That was perfect. Thanks.
July 1, 2010 at 10:27 pm
Thanks.. Now I understand.
I was wondering what happened to '-'
Also, why not it work like this:
'-' being string,
other two can implicitly convert to string?
July 2, 2010 at 12:42 am
You are welcome, Open Minded
Best Regards,
Chris Büttner
July 2, 2010 at 1:57 am
krishnaroopa (7/1/2010)
Also, why not it work like this:'-' being string,
other two can implicitly convert to string?
They can, but they won't. SQL Server uses well-defined rules for data type precedence for implicit conversions. Lower predence data types will always convert to higher precedence data types; never the other way around.
These rules are documented in Books Online. Use the index to find "data types [SQL Server]], precedence". Or read through the discussion, I believe I posted a link to this subject in the online version of BOL on one of the first pages.
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply