Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ISNUMERIC


ISNUMERIC

Author
Message
Andrew G
Andrew G
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2003 Visits: 2227
Does the CTE with UNION ALL increment i? It needs to increment in each column? I no comprehende
Toreador
Toreador
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2408 Visits: 8055
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' ;-)
firthr
firthr
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 99
hmmm, I'm lost on the logic on this one, could some step through it please?



Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8311 Visits: 11538
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
tommyh
tommyh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1560 Visits: 2000
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
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8311 Visits: 11538
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
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16374 Visits: 13199
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
tommyh
tommyh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1560 Visits: 2000
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
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8311 Visits: 11538
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
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8311 Visits: 11538
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
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