ISNUMERIC function

ISNUMERIC function

• Graham Tilson

  ISNUMERIC will return 1 for input values that can be converted to any of the numeric data types in SQL Server including the integer, float, decimal and money data types. So if you want to only check for specific conversions you need to roll your own check using LIKE or write maybe a SQLCLR based UDF in SQL Server 2005.
  
  

• Joost___

  Basically, if it can be cast as any numeric type, it will pass. e.g. SELECT CAST('-' AS SMALLINT) ------ 0 There are some really weird examples, but also a workaround: http://www.aspfaq.com/2390 wrote in message news:02a3c43d-ea59-43cf-b9a7-a4561f5f36d5@discussions.microsoft.com... > select isnumeric('-') returns 1 indicating valid numeric value, but > select cast('-' as numeric) returns Arithmetic overflow error > > Could someone explain this behaviour of SQL server > > Anand >
  

• Chip1958

  I cant explain why the isnumeric function works that way. (Though it is obvious how it is implemented. It simply scans for characters which are valid in a numeric expression. Isnumeric('+'), Isnumeric(',') and Isnumeric('.') all returns 1)
  
  However, You could use this ugly construct:
  
  select cast(cast('-' as int) as numeric)
  This will return 0.
  
  It wont work on '.' or ',' though.
  
  Hope this helps.
  
• ISNUMERIC function