SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What will be output?


What will be output?

Author
Message
Henry_Lee
Henry_Lee
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 1661
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 Sad
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 996
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
Daniel Bowlin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4106 Visits: 2629
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
Mike Dougherty
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 952
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
webrunner
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4157 Visits: 3886
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

Messages:
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.

Thanks,
webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1781 Visits: 1814
Thank you for an excellent explanation Hugo. The code like this should not be present anywhere near production of course, but the question is definitely a good one. One of the interesting side effects of implicit int to varchar conversions is that if the int value does not fit then varchar is set to *. For example, in the original example, if @a is set to 100 instead of 10 then it cannot fit into @b and this will cause @b = *, which can be somewhat confusing for someone who is unaware of this side effect. For example:

declare @a int;
declare @b varchar(2);

set @a = 100;
set @b = ' ' + @a + 2;

select @b;



The result is

----
*

This behavior is specific to int to varchar conversions only, other types such as smallint or bigint will cause the code to raise error.

Oleg
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4283 Visits: 3648
So given the following:

 
declare @a int
declare @b varchar(2)

set @a = 10
set @b = ' '
IF ISNUMERIC(@b) = 1
select @b +@a + 2;

ELSE Print 'Unknown'

-- Even though the following is the result
select @b +@a + 2;




The result is 'Unknown' after which the actual result, 12.
Seth Lynch
Seth Lynch
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1201 Visits: 603
As per Cliff Jones
The answer should be 12 not 22
(I see I missed the Set followed by Select
set @b = ' '+@a + 2
select @a + @b)

And as per Oleg Netchaev
With SSMS 2008 I got an * but not with SSMS 2005
john.arnott
john.arnott
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1974 Visits: 3059
Hugo Kornelis (3/25/2010)
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.

. . . .
Order of precedence dictates conversion of varchar to int, and ' ' is converted to the value 0
. . . .

Thanks for the detailed explanation, Hugo. You beat me to it in pointing out that spaces (or an empty string) cast to numeric as value zero. A slight twist on the QOD code would dramatically illustrate that the leading spaces are not at all ignored:

declare @a int
declare @b varchar(2)
set @a = 10
set @b = ' '+@a + 2
select @a + @b as Original
set @b = ' '*@a + 2 --Multiply instead
select @a + @b as Revised


Results are:
Original
-----------
22

(1 row(s) affected)

Revised
-----------
12

(1 row(s) affected)

SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32437 Visits: 18556
john.arnott (3/25/2010)
Hugo Kornelis (3/25/2010)
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.

. . . .
Order of precedence dictates conversion of varchar to int, and ' ' is converted to the value 0
. . . .

Thanks for the detailed explanation, Hugo. You beat me to it in pointing out that spaces (or an empty string) cast to numeric as value zero. A slight twist on the QOD code would dramatically illustrate that the leading spaces are not at all ignored:

declare @a int
declare @b varchar(2)
set @a = 10
set @b = ' '+@a + 2
select @a + @b as Original
set @b = ' '*@a + 2 --Multiply instead
select @a + @b as Revised


Results are:
Original
-----------
22

(1 row(s) affected)

Revised
-----------
12

(1 row(s) affected)


Another fine explanation of the conversion.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

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