Problem with IsNumeric in SQL 2005  
Author Message
tamzynb





PostPosted: Mon Oct 23 07:43:50 CDT 2006 Top

SQL Server Developer >> Problem with IsNumeric in SQL 2005

If you run the statement below in sql 2005 with the database compatibility
level set to 80 (SQL 2000) you get a 0, but if you change the compatibility
level to 90 (SQL 2005) it returns a 1. This sames to be a bug, Is there a
hot fix?
thank you
John


select isnumeric('\')

SQL Server166  
 
 
Adi





PostPosted: Mon Oct 23 07:43:50 CDT 2006 Top

SQL Server Developer >> Problem with IsNumeric in SQL 2005 Surprisingly I this is not a but, it is a feature. BOL states that
some characters will return the value 1:

"ISNUMERIC returns 1 for some characters that are not numbers, such as
dollar sign ($), plus (+), and minus (-)"

Adi

> If you run the statement below in sql 2005 with the database compatibility
> level set to 80 (SQL 2000) you get a 0, but if you change the compatibility
> level to 90 (SQL 2005) it returns a 1. This sames to be a bug, Is there a
> hot fix?
> thank you
> John
>
>
> select isnumeric('\')

 
 
Anith





PostPosted: Mon Oct 23 08:23:58 CDT 2006 Top

SQL Server Developer >> Problem with IsNumeric in SQL 2005 Adding to Adi's response, if you'd like to check for purely alphabetic
characters consider using PATINDEX rather than ISNUMERIC. Usage details and
syntax can be found in SQL Server Books Online.

--
Anith