14

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
  • Use TRY_PARSE instead. – Remus Rusanu Sep 17 '14 at 12:11
  • @RemusRusanu Only available on 2012+ of course – Mark Sinkinson Sep 17 '14 at 12:19
  • @MarkSinkinson correct, but OP mentions 2012 – Remus Rusanu Sep 17 '14 at 12:22
  • @RemusRusanu Sure, I was mentioning it in case other people came along to use the answer and missed that fact, then complained it didn't work :-) – Mark Sinkinson Sep 17 '14 at 12:26
  • 1
    Some possible explanations: A) The numeric status of the currency symbols is probably culture-dependent. B) Backslash is allowed for breaking strings onto multiple lines. – Keith Sep 17 '14 at 13:39
13

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.

  • 2
    And probably not fully known to a lot of the people with source code access, either. :-) Wish I could +1 again for the second paragraph. ISNUMERIC() is largely useless because its intention is to determine whether something can be converted to at least one numeric type; it is obviously much more important to know that you can convert to a single, specific numeric type. – Aaron Bertrand Sep 17 '14 at 18:31
  • 1
    @AaronBertrand There seems to be a reasonably significant number of cases where it does not even meet that intention too. – Martin Smith Sep 17 '14 at 20:08
9

The ASCII backslash (code point 5C) happens to share the same code point as the yen sign (¥) in the Shift-JIS encoding used by the Japanese version of Windows, and the won sign (₩) in Korean EUC-KR. Hence, it is very likely just a continuation of the currency sign theme.

  • Ah that's an interesting theory. It is money that it casts to as well. – Martin Smith Sep 17 '14 at 19:21
  • You're trolling us, right? Right?! :O – Jeroen Sep 17 '14 at 19:22
  • @Jeroen - It's on Wikipedia FWIW – Martin Smith Sep 17 '14 at 19:25
  • 3
    @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 – user47620 Sep 17 '14 at 19:26
  • Learn something new every day! :-) – Jeroen Sep 17 '14 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.