Monday, December 29, 2008

ISNUMERIC functions differently SQL 2000 to SQL 2005

Did you know that the ISNUMERIC function works differently between SQL Server 2000 and SQL Server 2005?  I didn't realize it until the other day when a post on the forums made me dig into it a little bit.

The difference is documented in the books online for sp_dbcmptlevel.

SQL Server 2000 and SQL Server 2005 Compatibility Level 80
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are significant.

For example, the following SELECT ISNUMERIC('121212,12') query returns 0. This indicates that the string 121212,12 is not numeric.

SQL Server 2005 Compatibility Level 90
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are ignored.

For example, the following SELECT ISNUMERIC('121212,12') query returns 1. This indicate that the string 121212,12 is numeric.

Pretty interesting, only the way to maintain the functionality if you actually expect it to return a zero (0) for commas means that you lose the ability to use things like CROSS APPLY, PIVOT, UNPIVOT, Common Table Expressions (CTE's), SQL CLR, and numerous other features in SQL Server 2005.  The only real way to go around this would be to change code to function differently and test for the commas or do a bulk update of the column to replace commas with a different symbol to force a failure or do an inline REPLACE in the SELECT.

No comments:

Post a Comment