What will be output?

  • Sunil Chandurkar

    Right there with Babe

    Points: 791

    Comments posted to this topic are about the item What will be output?


    SSC Guru

    Points: 281252

    Thanks for the Question.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • ziangij


    Points: 7243

    thanks :-)... its a good interview question.

  • SRatnaparkhi


    Points: 464

    Thanks good question :-). For info rmation if we write question like this

    declare @a int

    declare @B varchar(2)

    set @a = 10

    set @B = ' '+@a +' '+ 2+' '

    select @a +' '+ @B

    then also output will be same.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Good question, but unfortunately the explanation is not entirely correct. Especially this part is very misleading:

    Also the preceding space is not considered while performing the operation.

    Let's look in detail what happens to [font="Courier New"]set @B = ' '+@a + 2[/font]. Since all operators are the same, evaluation order is left to right. So the first partial expression to evaluate is [font="Courier New"]' '+@a[/font]. This mixes two datatypes: ' ' is varchar(1), and @a is int. Order of precedence dictates conversion of varchar to int, and ' ' is converted to the value 0 (run [font="Courier New"]SELECT CAST(' ' AS int);[/font] if you don't believe me). Since @a is set to 10, the result of this partial expression is 0 + 10, or 10; still types as int.

    The next step is to evaluate [font="Courier New"](' '+@a) + 2[/font] (parentheses added to emphasize evaluation order). We have already seen that [font="Courier New"](' '+@a)[/font] is equal to the integer value 10, so this is now reduced to [font="Courier New"]10 + 2[/font].

    As you have seen, there is no leading space to be considered anywhere. However, even if there was, the words "is not considered" is misleading. I think we would all agree that converting the values '5', ' 5' and ' 5 ' to integer should all result in the value 5 - not because we expect the leading and trailing spaces to be "not considered", but because considering them or not is irrelevant, as they have no impact on the interpretation of the string as a number.

    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/

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    ziangij (3/24/2010)

    thanks :-)... its a good interview question.

    I would not be too happy with these things being asked in an interview. My answer would be that mixing data types like this and taking a dependency hit on implied evaluation order and implicit conversion rules should not be done in production code.

    Code like this would get a red mark from me if I caught it in code review.

    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/

  • Bob JH Cullen


    Points: 2082

    I would certainly agree with Hugo - this just smells like bad practice if it ends up in production code. OK, the question is fine as an academic exercise, but just because you can do something like this doesn't mean you should, IMHO.

    btw - I got it wrong - probably because my style of coding doesn't give me enough practice at this kind of dubious T-SQL 😉

  • vk-kirov


    Points: 7686

    Hugo Kornelis (3/25/2010)

    ' ' is converted to the value 0 (run [font="Courier New"]SELECT CAST(' ' AS int);[/font] if you don't believe me).

    This is very interesting because [font="Courier New"]ISNUMERIC(' ')[/font] returns 0.

    On the one hand, SQL Server doesn't consider whitespaces as numeric values; on the other hand, it converts whitespaces to zero integer values. This is a little bit strange 🙂

  • ganeshanmsc

    SSC Enthusiast

    Points: 146

    The answer is 22

  • Sunil Chandurkar

    Right there with Babe

    Points: 791

    Thanks Hugo! your explaination is very helpful. also thanks for pointing out the misleading pharse. in future i can now be more careful for writing any stuff on SSC

  • Henry_Lee


    Points: 2714

    thanks ... its a good interview question

    IMHO the only way this a good interview question is if the answer you're looking for is "I'm not sure of the correct answer because I would not rely on this implicit conversion. If I'm performing calculations I would explicitly cast / convert to the appropriate datatype."

    Of course, I could just be bummed I got the question wrong 🙁

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    Hugo, your explanation is excellent, thank you. I wouldn't have thought it was any of those answers; my first guess was 112. I forgot about type precedence in the implicit conversion and simply read left to right, so I figured ' ' + @a would convert to ' 10' and then adding the 2 would result in ' 102'. So I've got type precedence pretty well hammered into my head now, thanks!

    Of course that's the secondary lesson. The primary lesson is never to allow code like this in your database.

    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Daniel Bowlin


    Points: 34566

    Interesting question and follow up discussion. Hugo thanks for the additional explanation. I find it very interesting that ' ' is converted to a zero during a confused typing operation such as this.

  • Mike Dougherty

    Ten Centuries

    Points: 1112

    Good question for challenging what we know to be right with an edge case.

    I second the opinion that code like this found in production warrants a red-card. Unfortunately we usually only find this stuff when whoever wrote it has left the company.

    I also got this question wrong. I assumed it was a trick. Had I just done the obvious math I would have selected the correct answer.

  • webrunner


    Points: 30368

    Great question. I got it wrong because I didn't know about the implicit conversion of string values containing only numbers.

    For example, this code throws the error I expected:

    declare @a int

    declare @B varchar(2)

    set @a = 10

    set @B = ' a'+@a + 2

    select @a + @B


    Server: Msg 245, Level 16, State 1, Line 5

    Syntax error converting the varchar value ' a' to a column of data type int.

    While answering the question, I thought only of those cases (probably the vast majority) where I had used code like the code above (for concatenation in printing test values only, not for adding numbers), and in variably it included at least one non-numeric character. So I was biased to think that such code would always throw an error.

    This question is a nice corrective for my ignorance in that regard.



    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 15 posts - 1 through 15 (of 35 total)

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