Menu
Recently I was working with ISNUMERIC in SQL Server, when I encountered a problem, which led to finding this snippet of code.
SELECT ISNUMERIC('.')
This returns 1, as in true, shouldn't this return 0 as in false?
817
SQL Server ISNUMERIC() Function The ISNUMERIC() function tests whether an expression is numeric. This function returns 1 if the expression is numeric, otherwise it returns 0.
Returns a Boolean value indicating whether an expression can be evaluated as a number. The required expressionargument is a Variant containing a numeric expression or string expression. IsNumeric returns True if the entire expression is recognized as a number; otherwise, it returns False.
Avoid using the IsNumeric() function, because it can often lead to data type conversion errors, when importing data. On SQL Server 2012 or later, use the Try_Convert() or Try_Cast() function instead.
Numerical data is a data type expressed in numbers, rather than natural language description. Sometimes called quantitative data, numerical data is always collected in number form.
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.
57
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
