How ISNUMERIC works???

Question

Post reply

How ISNUMERIC works???

Senthil T

Hall of Fame

Points: 3367
More actions
August 22, 2006 at 10:29 am

#116067

Hi,
I got an error email from my web application this morning. The error was '009282d2' cannot be converted to a column of data type int.
I tested this sample code and it does return 1. IF ISNUMERIC('009282d2') SELECT 'It is a number'
I tested with different values and this is what I found. It returns a 1, if the string starts with one or more numbers, followed by one d or e, followed by one or more numbers. Like this "\d+[d|e]\d+" ? Is there any other instance it would return a 1? And why is it doing this? May be d denotes a decimal and e denotes an exponent? Is there any other way to check whether a string is a valid integer without any alphabets?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Senthil T Hall of Fame Points: 3367 More actions · Answer 1

I am sorry, the sample code should be like this

IF ISNUMERIC('009282d2') = 1

SELECT 'It is a number'

Ram Ram Ten Centuries Points: 1079 More actions · Answer 2

You may try this out

DECLARE @MyTest VARCHAR(20)

SELECT @MyTest = '009282d1'

SELECT CASE WHEN (ISNUMERIC(@MyTest) = 1 AND CHARINDEX('D',@MyTest) = 0 AND CHARINDEX('E',@MyTest) = 0) THEN 1 ELSE 0 END

Microsoft, Please fix this bug!!!

Ram

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

More discusions available if you search for IsNumeric.

Samples.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=296033#bm296042

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=243646&p=1

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=897

Regards,
gova

Senthil T Hall of Fame Points: 3367 More actions · Answer 4

Senthil T

Hall of Fame

Points: 3367

August 22, 2006 at 2:02 pm

#656222

Thank you. I will use the ISINTEGER function.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

What ever are you talking about? There is no ISINTEGER function in MS-SQL Server....

IsNumeric allows anything that can mathematically be converted to a numberic value including scientific notation (e), decimal notation (d), commas, dollar signs, and a world of other things.

Replace your code of...

IF ISNUMERIC('009282d2') = 1

SELECT 'It is a number'

... with ...

IF '009282d2' NOT LIKE '%[^0-9]%'

SELECT 'It is a number'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David le Quesne SSCertifiable Points: 5229 More actions · Answer 6

Perhaps referring to the script on this site for an ISINTEGER UDF?

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=897

David

If it ain't broke, don't fix it...

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Yep... that would do it... that was the only 1 of the 3 that I did look at. Thanks, David.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)