Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


PRINT vagaries


PRINT vagaries

Author
Message
richard.maw
richard.maw
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 85
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;
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2952 Visits: 2629
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.
Richard M.
Richard M.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1114 Visits: 2668
Great question.... something new learned Smile

_______________________________________________________________________
For better assistance in answering your questions, click here
Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2212 Visits: 6490
Learned something new AGAIN :-P 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 :-D

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
ziangij
ziangij
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2906 Visits: 374
thanks... nice one
VM-723206
VM-723206
Say Hey Kid
Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)

Group: General Forum Members
Points: 662 Visits: 267
I agree with people saying that 5 would have been an interesting option! Smile
Open Minded
Open Minded
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 567
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.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8319 Visits: 11548
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
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'
'₧' / 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
Open Minded
Open Minded
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 567
@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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search