Why does ISNUMERIC(‘.’) return 1?

by

See IsNumeric() Broken? Only up to a point.

SELECT CAST('.' AS MONEY)

returns 0.00 (though the cast fails for int and float)

ISNUMERIC just checks that the value can be cast to any one of the numeric datatypes which is generally useless. Usually you want to know whether it can be cast to a specific type.

Additionally it doesn’t even seem to do that task correctly for all possible inputs.. ISNUMERIC(' ') returns 0 despite casting successfully to both int and money. Conversely ISNUMERIC(N'８') returns 1 but does not cast successfully to anything that I tried.

Some useful helper functions for that are here IsNumeric, IsInt, IsNumber.

SQL Server 2012 introduced TRY_PARSE and TRY_CONVERT that help with this greatly.

More Related Contents:

  1. How to create a specific SQL Server stored procedure? [closed]
  2. Creating stored procedure getting Incorrect syntax near the keyword ‘Declare’
  3. Should I use != or for not equal in T-SQL?
  4. Conversion failed when converting date and/or time from character string while inserting datetime
  5. Create a date from day month and year with T-SQL
  6. Foreign Key to non-primary key
  7. How should I pass a table name into a stored proc?
  8. Difference between left join and right join in SQL Server [duplicate]
  9. Dynamic SQL – EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)
  10. Get the last inserted row ID (with SQL statement) [duplicate]
  11. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  12. Get all dates between two dates in SQL Server
  13. Get the week start date and week end date from week number
  14. Run all SQL files in a directory
  15. Rename column SQL Server 2008
  16. SELECT FOR UPDATE with SQL Server
  17. How to group by month from Date field using sql
  18. unresolved reference to object [INFORMATION_SCHEMA].[TABLES]
  19. How to get the numeric part from a string using T-SQL?
  20. SQL Server Update Trigger, Get Only modified fields
  21. Grouped string aggregation / LISTAGG for SQL Server
  22. SQL Server SELECT LAST N Rows
  23. update columns values with column of another table based on condition [duplicate]
  24. SQL Row_Number() function in Where Clause
  25. SQL update trigger only when column is modified
  26. Passing dynamic order by in stored procedure
  27. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  28. Convert bigint to datetime
  29. SQL SERVER DATETIME FORMAT
  30. Insert all values of a table into another table in SQL

Leave a Comment