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 ««12345»»»

ISNUMERIC Expand / Collapse
Author
Message
Posted Monday, September 27, 2010 2:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:54 PM
Points: 1,193, Visits: 1,666
Does the CTE with UNION ALL increment i? It needs to increment in each column? I no comprehende
Post #993492
Posted Monday, September 27, 2010 2:34 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 1,739, Visits: 6,359
tommyh (9/27/2010)

True but atleast 1 and 1 are both numeric both in and out off context. Wheras "e" is only valid under some circumstances. With "11" you can do any SubString and still get a valid number. Try doing that on "2e1". Substring('2e1', 1, 2)... valid number... no.


You might equally complain that '$1' should not count as numeric, because you may have meant to type '41', and '1$1' is not numeric so therefore that character should not be allowed anyway.

While we're at it, '123' should not contain because you may have meant to type '124'
Post #993497
Posted Monday, September 27, 2010 2:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 27, 2010 8:38 AM
Points: 236, Visits: 99
hmmm, I'm lost on the logic on this one, could some step through it please?


Post #993500
Posted Monday, September 27, 2010 3:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 5,975, Visits: 8,236
tommyh (9/26/2010)
UMG Developer (9/25/2010)
Thanks for the question.

This should be good to help people understand how ISNUMERIC works...


<rant>
Yeah... like total crap. Its borderline useless. Just to further demonstrate that.
select isnumeric('2e1')

This gives 1! An 'e' isnt in my book numeric. Now i know SQL thinks its a calculator and thinks '2e1' = 20 (it does the same with a 'd' instead of the 'e'). But seriously, thats stupid.

ISNUMERIC is indeed useless (I even removed the "borderline" here). But not for the reasons you state. The reason it is unseles, is that it tells you if a string can be converted to "any" numerical data type, without bothering to tell you which data type. And since conversion rules are different for different types, a result of ISNUMER(...) = 1 does not guarantee a good conversion.

SQL Server does not think it is a calculator. SQL Server recognises "2e1" as a valid form to input floating point data. The "2e1" is actually shorthand for "2 x 101". In this particular case, writing "20" would have been easier. But do you really prefer "0.0000000000000000000012" over "1.2e-21"? And do you prefer "123000000000000000000000000000000" over "1.23e32"? For those are values well within the data permitted range of floating point numbers.
And another reason for writing "1e2" might be that you need the constant to be regarded as floating point data, to prevent conversions. Just as we sometimes type "3.0" instead of just "3" to be sure that SQL Server treates the constant as numeric and not as int. And just as we type "N'string'" instead of "'string'" to force unicode data.

The "2e1" form is recognised by almost every programming language I have ever used. As is the "2d1" form (the difference is that one is double precision, the other normal precision - I don't know off the top of my head how and even if this translates to different data types in SQL Server, but at least for code compatbility, it recognises both forms).


Like you, I think ISNUMERIC in its current form sucks. I really want Microsoft to give us a function that tells me if I can convert a specific string value in a specific data type. One that would permit a decimal point for conversion to numeric, but not for conversion to integer. And one that would permit "2e1" for conversion to float, but not to any other data type.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #993512
Posted Monday, September 27, 2010 3:18 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
Toreador (9/27/2010)
tommyh (9/27/2010)

True but atleast 1 and 1 are both numeric both in and out off context. Wheras "e" is only valid under some circumstances. With "11" you can do any SubString and still get a valid number. Try doing that on "2e1". Substring('2e1', 1, 2)... valid number... no.


You might equally complain that '$1' should not count as numeric, because you may have meant to type '41', and '1$1' is not numeric so therefore that character should not be allowed anyway.

While we're at it, '123' should not contain because you may have meant to type '124'


Yeah but i thought one exampel why this function sucks was enough.

And yes i dont think $1 should count as numeric. $ is a currency symbol not a number. Its a property of a number but not part of the number itself (its not like that gets stored with the number anyway if you convert it to Int/Decimal... not even if you store it as money). In that case why not allow suffixes and we could really screw things up. Like 1' or 1K (both represent 1000).

/T
Post #993514
Posted Monday, September 27, 2010 3:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 5,975, Visits: 8,236
tommyh (9/27/2010)
da-zero (9/27/2010)
Good question, although I seem to remember that I've seen similar ones.

But I don't think that considering scientific notation as numeric is stupid. 2e1 is just another way writing 20, so it is still a number.
I think the reason why you can't immediately convert it to an int is because of how SQL Server handles the scientific notation. But I can't seem to find any references on that.


And how exactly do you know that '2e1' is a "scientific notation"? Could be there by accident so the value should actually have been 21 (blackjack anyone?). Now that error just slipped us by because SQL "tries" to think.

The aim of ISNUMERIC is not to prevent erroneous data. It could never do that - the way the keys are arranged, "231" is a much more probable type than "2e1" if you want to enter 21.
ISNUMERIC is intended to prevent run-time errors when converting data from an untrusted source to a numeric data type. It fails miserably at that goal, obviously - but if you're going to bash a function,. then at least use the proper arguments.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #993515
Posted Monday, September 27, 2010 3:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:16 PM
Points: 13,616, Visits: 10,505
tommyh (9/27/2010)

And yes i dont think $1 should count as numeric. $ is a currency symbol not a number.


+ / * - % are arithmetic operators, not numbers. So 1+1 should still be rejected in your opinion. What you are looking for is a function that tests if a given expression matches the pattern '[0-9]' for every character. That is not what isnumeric() is intended for (as Hugo already mentioned). It is similar to complaining that a hammer cannot tighten a screw.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #993522
Posted Monday, September 27, 2010 3:42 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
Hugo Kornelis (9/27/2010)
tommyh (9/27/2010)
da-zero (9/27/2010)
Good question, although I seem to remember that I've seen similar ones.

But I don't think that considering scientific notation as numeric is stupid. 2e1 is just another way writing 20, so it is still a number.
I think the reason why you can't immediately convert it to an int is because of how SQL Server handles the scientific notation. But I can't seem to find any references on that.


And how exactly do you know that '2e1' is a "scientific notation"? Could be there by accident so the value should actually have been 21 (blackjack anyone?). Now that error just slipped us by because SQL "tries" to think.

The aim of ISNUMERIC is not to prevent erroneous data. It could never do that - the way the keys are arranged, "231" is a much more probable type than "2e1" if you want to enter 21.
ISNUMERIC is intended to prevent run-time errors when converting data from an untrusted source to a numeric data type. It fails miserably at that goal, obviously - but if you're going to bash a function,. then at least use the proper arguments.


I dont agree with your arguments either. A string is either a number or its not. Not depending on what you try and convert it to. It should be one or the other.

So you think it sucks for your reasons and i think it sucks for my reasons. Who is right... i am off course

/T
Post #993524
Posted Monday, September 27, 2010 3:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 5,975, Visits: 8,236
da-zero (9/27/2010)
tommyh (9/27/2010)

And yes i dont think $1 should count as numeric. $ is a currency symbol not a number.


+ / * - % are arithmetic operators, not numbers. So 1+1 should still be rejected in your opinion.

It is. ISNUMERIC('1+1') returns 0. And that makes sense, because '1+1' does not convert to any of the numerical data types. A + (or -) is only permitted before the first number character. (Actually, the rule is a bit more complex than that). You can convert '+123' or '-123' to numeric, but not '12+3' or '12-3'.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #993525
Posted Monday, September 27, 2010 4:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 5,975, Visits: 8,236
tommyh (9/27/2010)
I dont agree with your arguments either. A string is either a number or its not. Not depending on what you try and convert it to. It should be one or the other.

If that's your point of view, then you should be happy with the current implementation of ISNUMERIC. "2e1" is a valid number.

I already said that all programming languages I have worked with recognise 1e2 as a floating point representation for 20 (1 x 102). I had at that time not yet verified your assertion that ".NET doesnt do this. Old VB6 does...". I have now. Using Visual Studio 2005, I opened a VB.Net project, edited a line to include the numeric constant "1e3", and I Visual Studio automatically changed it to "1000.0". I then tried the same with "1.23e-15", and VisualStudio changed that to "1.23E-15". Both versions could be compiled and executed.

I then tried the same with a C# project. This time, the code was not changed, but it still compiled and executed without problems.

So, I don't know who made you believe that .NET "doesnt do this" - but if I were you, I'd start double checking all other information from the same source, for it is apparently not the most reliable authority on .NET.


That all being said, I completely fail to see the acutal use of a function that tells me that a string value "is either a number or its not". What good is it to know that a value is a number if I can still not convert it to a numerical data type without the risk of getting a runtime error? You may see a good use for such a function, but I don't.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #993535
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse