Why Does Isnumeric('.') Return 1

Why does ISNUMERIC('.') return 1?

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.

I have numeric value with $100. When I check isnumeric it returns as 1 but I need to consider this as varchar value

If you use MS SQL 2012 or higher you could use TRY_CONVERT and TRY_CAST functions:

DECLARE @var varchar(100);
SET @var = '$1000';

SELECT 
    ISNULL( TRY_CAST(@var AS numeric(36, 4)), 0 )

Returns a value cast to the specified data type if the cast succeeds;
otherwise, returns null.

SQL Server ISNUMERIC returning wrong data

If you are looking for strings of digits, don't use isnumeric(). Just use like:

select (case when col like '%[^0-9]%' then 0 else 1 end) as IsAllDigits

If you want to try a conversion, then use try_convert():

select (case when try_convert(bigint, col) is null then 0 else 1 end) as IsConvertable

Why ISNumeric() Transact-SQL function treats some var-chars as Int

ISNUMERIC() has some flaws. It can return True/1 for values that are clearly not numbers.

SELECT 
ISNUMERIC('.'), 
ISNUMERIC('$')

If this is causing you issues, try using TRY_PARSE()

SELECT 
TRY_PARSE('2' AS INT)

You can use this and filter for non-null results.

SELECT some_column 
FROM some_table 
WHERE TRY_PARSE(some_column AS INT) IS NOT NULL

SQL IsNumeric Returns True but SQL Reports 'Conversion Failed'

You need to replace comma with a period:

CAST(REPLACE(column, ',', '.') AS FLOAT)

SQL Server outputs decimal separator defined with locale, but does not unterstand anything but a period in CASTs to numeric types.

SQL IsNumeric not working

It seems that isnumeric has some Problems:

http://www.sqlhacks.com/Retrieve/Isnumeric-problems
(via internet archive)

According to that Link you can solve it like that:

select
cast(Reserve as decimal)
from MyReserves
where MyReserves is not null
and MyReserves * 1 = MyReserves 

---

Related Topics