SQL Server's Isnumeric Function

SQL IsNumeric function

472369326D4 is a valid float type. The D4 is translated as adding four 0 values, effectively multiplying the value before the D character by 10000.

Example Sql

SELECT cast('472369326D4' as float) 
SELECT cast('472369326D3' as float) 
SELECT cast('472369326D2' as float) 

Output:

4723693260000
472369326000
47236932600

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.

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/

Efficient ISNUMERIC() replacements on SQL Server?

You can use the T-SQL functions TRY_CAST() or TRY_CONVERT() if you're running SQL Server 2012 as Bacon Bits mentions in the comments:

SELECT CASE WHEN TRY_CAST('foo' AS INT) IS NULL THEN 0 ELSE 1 END

SELECT CASE WHEN TRY_CAST(1 AS INT) IS NULL THEN 0 ELSE 1 END

If you're using SQL 2008 R2 or older, you'll have to use a .NET CLR function, and wrap System.Decimal.TryParse().

CAST and IsNumeric

IsNumeric returns 1 if the varchar value can be converted to ANY number type. This includes int, bigint, decimal, numeric, real & float.

Scientific notation could be causing you a problem. For example:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values(NULL)
Insert Into @Temp Values('1')
Insert Into @Temp Values('1e4')
Insert Into @Temp Values('Not a number')

Select Cast(Data as bigint)
From   @Temp
Where  IsNumeric(Data) = 1 And Data Is Not NULL

There is a trick you can use with IsNumeric so that it returns 0 for numbers with scientific notation. You can apply a similar trick to prevent decimal values.

IsNumeric(YourColumn + 'e0')

IsNumeric(YourColumn + '.0e0')

Try it out.

SELECT CAST(myVarcharColumn AS bigint)
FROM myTable
WHERE IsNumeric(myVarcharColumn + '.0e0') = 1 AND myVarcharColumn IS NOT NULL
GROUP BY myVarcharColumn

ISNUMERIC function with Tab char(9)

Not only Char(9),it does for all of the values below..

SELECT [Ascii Code]        = STR(Number),
        [Ascii Character]   = CHAR(Number),
        [ISNUMERIC Returns] = ISNUMERIC(CHAR(Number))
   FROM Master.dbo.spt_Values
  WHERE Type = 'P'
    AND Number BETWEEN 0 AND 255
    AND ISNUMERIC(CHAR(Number)) = 1

Ascii Code Ascii Character ISNUMERIC Returns 
---------- --------------- ----------------- 
         9                 1
        10                 1
        11                 1
        12                 1
        13                 1
        36 $               1
        43 +               1
        44 ,               1
        45 -               1
        46 .               1
        48 0               1
        49 1               1
        50 2               1
        51 3               1
        52 4               1
        53 5               1
        54 6               1

From the Official docs..emphasis on highlighted..

ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types

So Isnumeric will return 1 if it returns any one of the type in highlighted part above..

Jeff moden Describes the why part here..

There are many different combinations of letters, numbers, and symbols that can actually be converted to numeric data types and ISNUMERIC will return a "1" for all of them. It's not a flaw... that's the way it's supposed to work!

References:

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

SQL Server ISNUMERIC() Clarification

You should use CAST() or TRY_CAST() instead:

declare @test nvarchar(8) = '12345678'
select cast(@test as bigint) -- errors on failure
select try_cast(@test as bigint) -- returns null on failure

Also, important to point out the ISNUMERIC() isn't perfect. From the docs:

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).

For this reason I don't think the logical check is of value here. Best to use TRY_CAST() on all values, regardless of the presence of characters and handle the null response in a predictable manner.

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

---

Related Topics