SQL Isnumeric Not Working

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 

SQL Server's ISNUMERIC function

Unfortunately, the ISNUMERIC() function in SQL Server has many quirks. It's not exactly buggy, but it rarely does what people expect it to when they first use it.

However, since you're using SQL Server 2012 you can use the TRY_PARSE() function which will do what you want.

This returns NULL:
SELECT TRY_PARSE('7860D399' AS int)

This returns 7860399
SELECT TRY_PARSE('7860399' AS int)

https://msdn.microsoft.com/en-us/library/hh213126.aspx

Obviously, this works for datatypes other than INT as well. You say you want to check that a value is numeric, but I think you mean INT.

Issue with IsNumeric in t-sql?

you should use try_cast instead, because ISNUMERIC evaluates to 1 for 5031,

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see money and smallmoney (Transact-SQL).
DECLARE @Str NVARCHAR(20)

SET @Str = 'XYZ 505031, some text'

SELECT 
   CASE 
      WHEN TRY_CAST(SUBSTRING(@Str,6,7) AS DECIMAL(10,2))  IS NOT NULL 
      THEN CONVERT(int,SUBSTRING(@Str,6,7))  
      ELSE 0 
   END, 
   SUBSTRING(@Str,6,7)

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.

ISNUMERIC on VARCHAR Column returning numbers still?

The IsNumeric function in SQL Server only returns true when the expression is a valid numeric type. It doesn't understand that the expression is 1 and seven eighths. It sees this as a string. MSDN IsNumeric Reference

IsNumeric does not work in SQL server

Use single quotes for strings!

select dbo.OffsetKPL('100',200)

If you have QUOTED_IDENTIFIER on (the default) things in double quotes are expected to be object names.

isnumeric may not be what you need though as all kinds of unexpected things return 1 for this.

SELECT ISNUMERIC('$'), ISNUMERIC('.'), 
       ISNUMERIC('12d5'), ISNUMERIC(','), ISNUMERIC('1e1')

See IsNumeric() Broken? Only up to a point for some discussion on this point.

SQL Server's ISNUMERIC function, with just a minus-sign

You can make a simple check:

;WITH cte AS (
SELECT 1 as num
UNION ALL
SELECT num+1
FROM cte
WHERE num< 256
)

SELECT  num,
        CHAR(num),
        ISNUMERIC(CHAR(num))
FROM cte
WHERE ISNUMERIC(CHAR(num)) = 1 
OPTION (MAXRECURSION 1000)

This will show that not only numbers are considered valid numeric type:

$
+
,
-
.
\
€
¤

One of the workarounds is to use NOT LIKE '%[^0-9]%'

F.e. if I add this nd CHAR(num) NOT LIKE '%[^0-9]%' to WHERE statement of below query it will return numbers from 0 to 9

Or use TRY_PARSE(.. as int)

Some links:

• https://msdn.microsoft.com/en-us/library/hh213126.aspx

• http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

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.

---

Related Topics