PRINT vagaries

  • 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;

  • 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.

  • Great question.... something new learned 🙂

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • 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)[/url]

  • thanks... nice one

  • I agree with people saying that 5 would have been an interesting option! 🙂

  • 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.

  • 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: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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

  • @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?

  • 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

  • @christian That was perfect. Thanks.

  • 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?

  • You are welcome, Open Minded

    Best Regards,

    Chris Büttner

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 31 through 45 (of 49 total)

You must be logged in to reply to this topic. Login to reply