What will be output?

Question

What will be output?

Viewing 15 posts - 16 through 30 (of 34 total)

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

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 1

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-2 and this will cause @b-2 = *, which can be somewhat confusing for someone who is unaware of this side effect. For example:

declare @a int;

declare @b-2 varchar(2);

set @a = 100;

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

select @b-2;

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 SSChampion Points: 10547 More actions · Answer 2

So given the following:

declare @a int

declare @b-2 varchar(2)

set @a = 10

set @b-2 = ' '

IF ISNUMERIC(@b) = 1

select @b-2 +@a + 2;

ELSE Print 'Unknown'

-- Even though the following is the result

select @b-2 +@a + 2;

The result is 'Unknown' after which the actual result, 12.

Seth Lynch SSCrazy Points: 2975 More actions · Answer 3

As per Cliff Jones

The answer should be 12 not 22

(I see I missed the Set followed by Select

set @b-2 = ' '+@a + 2

select @a + @b-2)

And as per Oleg Netchaev

With SSMS 2008 I got an * but not with SSMS 2005

john.arnott SSChampion Points: 11882 More actions · Answer 4

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-2 varchar(2)

set @a = 10

set @b-2 = ' '+@a + 2

select @a + @b-2 as Original

set @b-2 = ' '*@a + 2 --Multiply instead

select @a + @b-2 as Revised

Results are:

Original

-----------

22

(1 row(s) affected)

Revised

-----------

12

(1 row(s) affected)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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-2 varchar(2)
set @a = 10
set @b-2 = ' '+@a + 2
select @a + @b-2 as Original
set @b-2 = ' '*@a + 2 --Multiply instead
select @a + @b-2 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[/url]
Learn Extended Events

pjdiller SSC Eights! Points: 977 More actions · Answer 6

Representing those of us who are still trying to catch up when it comes to T-SQL, I would like to say that this is a frustrating excercise, which is no fault of Sunil Chandurkar.

I wish SQL would be a little more strict than "smart" (look what I can pull out of a string and add :-)). I agree with several others in this conversation that would look negatively on depending on this type of implicit conversion.

I do appreciate the information! Thanks.

john.arnott SSChampion Points: 11882 More actions · Answer 7

pjdiller (3/25/2010)
Representing those of us who are still trying to catch up when it comes to T-SQL, I would like to say that this is a frustrating excercise, which is no fault of Sunil Chandurkar.
I wish SQL would be a little more strict than "smart" (look what I can pull out of a string and add :-)). I agree with several others in this conversation that would look negatively on depending on this type of implicit conversion.
I do appreciate the information! Thanks.

But, then again, the implicit conversion features let us create little gems like this:

Select 3*3e3+3e3/3ee3e,3e3e3e3e

Try to parse that manually before you copy/paste to SSMS.

John Carlson-431129 SSChasing Mays Points: 613 More actions · Answer 8

vk-kirov (3/25/2010)
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 🙂

This is consistent with the explanation though. It is not considered a numeric until is implicitly converted through a numeric operation with a numeric operand. So, ISNUMERIC(' ') will return 0, but ISNUMERIC(' ' + @a + 2) will return 1.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 9

john.arnott (3/25/2010)
But, then again, the implicit conversion features let us create little gems like this:
Select 3*3e3+3e3/3ee3e,3e3e3e3e
Try to parse that manually before you copy/paste to SSMS.

My eyes! The goggles do nothing!

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: 64780 More actions · Answer 10

John Carlson-431129 (3/25/2010)
vk-kirov (3/25/2010)
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 🙂
This is consistent with the explanation though. It is not considered a numeric until is implicitly converted through a numeric operation with a numeric operand. So, ISNUMERIC(' ') will return 0, but ISNUMERIC(' ' + @a + 2) will return 1.

No, it is not consistent. According to Books Online, "A return value of 1 indicates that expression can be converted to at least one of the numeric types". Since a space can, apparently, be converted at least to integer, ISNUMERIC should return 1.

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/

John Carlson-431129 SSChasing Mays Points: 613 More actions · Answer 11

Hugo Kornelis (3/25/2010)
John Carlson-431129 (3/25/2010)
vk-kirov (3/25/2010)
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 🙂
This is consistent with the explanation though. It is not considered a numeric until is implicitly converted through a numeric operation with a numeric operand. So, ISNUMERIC(' ') will return 0, but ISNUMERIC(' ' + @a + 2) will return 1.
No, it is not consistent. According to Books Online, "A return value of 1 indicates that expression can be converted to at least one of the numeric types". Since a space can, apparently, be converted at least to integer, ISNUMERIC should return 1.

Hmmm. I guess what I am trying to say, is that the ' ' cannot be numeric on its own, but only when combined with a numeric operand. (A parasite, if you will.) So, I still contend that it is consistent to return 0 if there is no numeric operand combined with it.

That said, I think it is a bad idea to even allow this to happen in the first place.

abrazier SSC Enthusiast Points: 112 More actions · Answer 12

John Carlson-431129 (3/25/2010)
Hugo Kornelis (3/25/2010)
John Carlson-431129 (3/25/2010)
vk-kirov (3/25/2010)
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 🙂
This is consistent with the explanation though. It is not considered a numeric until is implicitly converted through a numeric operation with a numeric operand. So, ISNUMERIC(' ') will return 0, but ISNUMERIC(' ' + @a + 2) will return 1.
No, it is not consistent. According to Books Online, "A return value of 1 indicates that expression can be converted to at least one of the numeric types". Since a space can, apparently, be converted at least to integer, ISNUMERIC should return 1.
Hmmm. I guess what I am trying to say, is that the ' ' cannot be numeric on its own, but only when combined with a numeric operand. (A parasite, if you will.) So, I still contend that it is consistent to return 0 if there is no numeric operand combined with it.
That said, I think it is a bad idea to even allow this to happen in the first place.

But:

[font="Courier New"]SELECT ISNUMERIC(CAST(' ' AS int))[/font]

returns 1, so the space can be cast/converted on its own to int (as we know) and, as Hugo says, ISNUMERIC(' ') should return 1 based on the BOL description. So the description doesn't match the behaviour, in this case.

pjdiller SSC Eights! Points: 977 More actions · Answer 13

pjdiller

SSC Eights!

Points: 977

March 26, 2010 at 8:58 am

#1140423

just let me say... wow. scary.

Paul White SSC Guru Points: 150467 More actions · Answer 14

Wouldn't it be fun if T-SQL were strongly typed?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 15

I LOVE implicit conversions! 😀

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems