Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

PRINT vagaries Expand / Collapse
Author
Message
Posted Wednesday, June 30, 2010 10:34 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 18, 2010 8:24 AM
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;
Post #945643
Posted Wednesday, June 30, 2010 11:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
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.
Post #945654
Posted Wednesday, June 30, 2010 12:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:56 AM
Points: 1,093, Visits: 2,617
Great question.... something new learned :)



_______________________________________________________________________
For better assistance in answering your questions, click here
Post #945708
Posted Wednesday, June 30, 2010 4:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
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


--------------------------------------------------------------------------
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)
Post #945851
Posted Wednesday, June 30, 2010 11:18 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:40 AM
Points: 2,060, Visits: 370
thanks... nice one
Post #945924
Posted Thursday, July 1, 2010 12:49 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
I agree with people saying that 5 would have been an interesting option! :)
Post #945953
Posted Thursday, July 1, 2010 12:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:04 AM
Points: 176, 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.
Post #945957
Posted Thursday, July 1, 2010 1:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:07 AM
Points: 6,040, Visits: 8,322
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
Post #945978
Posted Thursday, July 1, 2010 1:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #945980
Posted Thursday, July 1, 2010 2:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:04 AM
Points: 176, 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?
Post #945989
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse