March 25, 2010 at 9:05 am
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;
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
March 25, 2010 at 10:00 am
March 25, 2010 at 10:50 am
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
set @b-2 = ' '*@a + 2 --Multiply instead
Results are:
Original
-----------
22
(1 row(s) affected)
Revised
-----------
12
(1 row(s) affected)
March 25, 2010 at 11:01 am
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
set @b-2 = ' '*@a + 2 --Multiply instead
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
March 25, 2010 at 11:30 am
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.
March 25, 2010 at 12:25 pm
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.
March 25, 2010 at 2:32 pm
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.
March 25, 2010 at 3:07 pm
john.arnott (3/25/2010)
But, then again, the implicit conversion features let us create little gems like this:
Select 3*3e3+3e3/3ee3e,3e3e3e3eTry to parse that manually before you copy/paste to SSMS.
My eyes! The goggles do nothing!
March 25, 2010 at 3:09 pm
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.
March 25, 2010 at 8:48 pm
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.
March 26, 2010 at 8:23 am
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.
March 26, 2010 at 8:58 am
just let me say... wow. scary.
March 26, 2010 at 12:11 pm
Wouldn't it be fun if T-SQL were strongly typed?
March 29, 2010 at 12:47 pm
I LOVE implicit conversions! 😀
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply