What is the logic behind ISNUMERIC for certain special characters?

The `ISNUMERIC` function has some unexpected behavior. The MSDN documentation says:

`ISNUMERIC` returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid numeric data types include the following: int, bigint, smallint, tinyint, decimal, numeric, money, smallmoney, float, real.

And it also has a footnote:

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

Okay, so `+`, `-`, and listed currency symbols are expected to be considered numeric. So far so good.

Now for the odd part. First up, some of the currency symbols from linked article are not numeric, including:

• Euro-Currency sign, hex 20A0: `₠`
• Naira sign, hex 20A6: `₦`
• Rial sign, hex FDFC: `﷼`

This is weird, and I can't seem to find out why? Is this version or environment dependent?

However, things get weirder. Here are a few others I can't explain:

• `/` is not numeric, but `\` is (Huh?!)
• `REPLICATE(N'9', 308)` is numeric, but `REPLICATE(N'9', 309)` is not

The first and most basic question is: what explains the above cases? More importantly though: what is the logic behind `ISNUMERIC`, so I could explain / predict all cases myself?

Here's a good way to reproduce things:

``````DECLARE @tbl TABLE(txt NVARCHAR(1000));

INSERT INTO @tbl (txt)
VALUES (N''), (N' '), (N'€'), (N'\$'), (N'\$\$'),
(NCHAR(8356)), (NCHAR(8352)), (NCHAR(8358)), (NCHAR(65020)),
(N'+'), (N'-'), (N'/'), (N'\'), (N'_'), (N'e'), (N'1e'), (N'e1'), (N'1e1'),
(N'1'), (N'-1'), (N'+1'), (N'1+1'), (N'⒈'), (N'🄂'), (N'¹'), (N'①'), (N'½'),
(N'🎅'), (REPLICATE(N'9', 307)), (REPLICATE(N'9', 308)), (REPLICATE(N'9', 309)),
(REPLICATE(N'9', 310));

SELECT  UNICODE(LEFT(txt, 1)) AS FirstCharAsInt,
LEN(txt) AS TxtLength,
txt AS Txt,
ISNUMERIC(txt) AS [ISNUMERIC]
FROM    @tbl;
``````

When I run this on my local Sql Server 2012 box I get the following results:

``````FirstCharAsInt   TxtLength   Txt        ISNUMERIC
---------------  ----------  ---------  ----------
NULL             0                      0
32               0                      0
8364             1           €          1
36               1           \$          1
36               2           \$\$         0
8356             1           ₤          1
8352             1           ₠          0  --??
8358             1           ₦          0  --??
65020            1           ﷼‎          0  --??
43               1           +          1
45               1           -          1
47               1           /          0
92               1           \          1  --??
95               1           _          0
101              1           e          0
49               2           1e         0
101              2           e1         0
49               3           1e1        1
49               1           1          1
45               2           -1         1
43               2           +1         1
49               3           1+1        0
9352             1           ⒈         0
55356            2           🄂          0
185              1           ¹          0
9312             1           ①          0
189              1           ½          0
55356            2           🎅         0
57               307        /*...*/     1
57               308        /*...*/     1  --??
57               309        /*...*/     0  --??
57               310        /*...*/     0
``````
• The only ones that seem incorrect to me are that it falsely reports `0` for five of the values that actually cast fine to `money`. The others seem accurate. SQL FIDDLE Sep 17, 2014 at 18:43
• Though from `NCHAR(0) - NCHAR(65535)` I see 112 discrepancies. Including characters such as `₁,₂,₃,４,５,６,７,８,９` which look numeric but don't cast successfully to anything for me. Fiddle Sep 17, 2014 at 20:00

The detailed behaviours of `ISNUMERIC` are not documented, and probably not fully known to anyone without source code access. That said, it may be that interpretation depends on the Unicode categorization (numeric or not). Equally, the weird cases you mention may be bugs that are preserved for backwards compatibility. Yes I know that sounds crazy, but it does happen.
As you are using SQL Server 2012, there is no need to use `ISNUMERIC`. Use `TRY_CONVERT` or the synonymous `TRY_CAST` instead to check if a string is convertible to a given type. Where they provide adequate functionality, these are preferable to `TRY_PARSE`, because the latter involves more expensive processing via CLR integration.
• Ah that's an interesting theory. It is `money` that it casts to as well. Sep 17, 2014 at 19:21
• @Jeroen I'm afraid not. Switch the legacy code page of your Windows installation to Japanese and you get paths such as `C:¥Program Files¥` in explorer.exe Sep 17, 2014 at 19:26