ISNUMERIC

Question

Post reply

ISNUMERIC

sknox

SSChampion

Points: 12388
More actions
September 25, 2010 at 12:14 pm

#229749

Comments posted to this topic are about the item ISNUMERIC

Viewing 15 posts - 1 through 15 (of 43 total)

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

UMG Developer SSChampion Points: 13482 More actions · Answer 1

Thanks for the question.

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

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 2

Dhruvesh Shah

SSCrazy

Points: 2303

September 26, 2010 at 10:18 pm

#1227502

Good Question I got lost in Union all.

subbu_e SSCrazy Points: 2431 More actions · Answer 3

yes this really good question

Regards,
Subbu
Click here to Get Speedy answer or solution

tommyh SSCertifiable Points: 6302 More actions · Answer 4

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.

Even more stupid is that even though SQL thinks this is 20 you cant convert it to an Int. You can however convert it to a Float... then to an Int... sigh.

.NET doesnt do this. Old VB6 does... but atleast VB accepts it as a Int so its consistant. SQL is divided and isnt sure abouts its decision if it really is numeric or not.

</rant off>

/T

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

tommyh SSCertifiable Points: 6302 More actions · Answer 6

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.

/T

paul.knibbs SSCoach Points: 15320 More actions · Answer 7

da-zero (9/27/2010)
Good question, although I seem to remember that I've seen similar ones.

I'm sure there's been a very similar one in the past two or three months--I forget exactly when it was, though.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

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.
/T

How do you know 1+1 isn't 11 instead of 2? Maybe someone just typed in a + by accident. Let's just abandon all arithmetic operators, because SQL Server tries to think... :rolleyes:

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

tommyh SSCertifiable Points: 6302 More actions · Answer 9

da-zero (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.
/T
How do you know 1+1 isn't 11 instead of 2? Maybe someone just typed in a + by accident. Let's just abandon all arithmetic operators, because SQL Server tries to think... :rolleyes:

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.

/T

Andrew G SSChampion Points: 12809 More actions · Answer 10

Does the CTE with UNION ALL increment i? It needs to increment in each column? I no comprehende

Toreador SSChampion Points: 11365 More actions · Answer 11

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 SSCommitted Points: 1606 More actions · Answer 12

hmmm, I'm lost on the logic on this one, could some step through it please?

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 13

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 10¹". 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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

tommyh SSCertifiable Points: 6302 More actions · Answer 14

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