Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

What will be output? Expand / Collapse
Author
Message
Posted Thursday, March 25, 2010 11:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, March 15, 2014 1:45 PM
Points: 405, Visits: 286
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.
Post #890071
Posted Thursday, March 25, 2010 12:25 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,521, Visits: 3,039
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.
Post #890147
Posted Thursday, March 25, 2010 2:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 8:34 AM
Points: 101, Visits: 192
vk-kirov (3/25/2010)
Hugo Kornelis (3/25/2010)
' ' is converted to the value 0 (run SELECT CAST(' ' AS int); if you don't believe me).

This is very interesting because ISNUMERIC(' ') 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.
Post #890237
Posted Thursday, March 25, 2010 3:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:07 PM
Points: 6,002, Visits: 8,266
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #890260
Posted Thursday, March 25, 2010 3:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:07 PM
Points: 6,002, Visits: 8,266
John Carlson-431129 (3/25/2010)
vk-kirov (3/25/2010)
Hugo Kornelis (3/25/2010)
' ' is converted to the value 0 (run SELECT CAST(' ' AS int); if you don't believe me).

This is very interesting because ISNUMERIC(' ') 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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #890261
Posted Thursday, March 25, 2010 8:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 8:34 AM
Points: 101, Visits: 192
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 SELECT CAST(' ' AS int); if you don't believe me).

This is very interesting because ISNUMERIC(' ') 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.
Post #890356
Posted Friday, March 26, 2010 8:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 7, 2014 6:59 AM
Points: 24, Visits: 49
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 SELECT CAST(' ' AS int); if you don't believe me).

This is very interesting because ISNUMERIC(' ') 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:

SELECT ISNUMERIC(CAST(' ' AS int))

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.
Post #890673
Posted Friday, March 26, 2010 8:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, March 15, 2014 1:45 PM
Points: 405, Visits: 286
just let me say... wow. scary.
Post #890719
Posted Friday, March 26, 2010 12:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:59 AM
Points: 11,194, Visits: 11,166
Wouldn't it be fun if T-SQL were strongly typed?



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #890964
Posted Monday, March 29, 2010 12:47 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
I LOVE implicit conversions!

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Post #892112
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse