ISNUMERIC Function Returns 1 for 12e5: What to Use Instead

The ISNUMERIC function returns 1 for strings such as 12e5, a lone dollar sign and a lone plus. It is not broken. It answers a wider question than the one most people ask.

The Question ISNUMERIC Answers

People use ISNUMERIC to ask whether a string is a number they can add up. A report that filters on it fails when the data holds a value like 12e5. The ISNUMERIC function returns 1 there, because 12e5 is scientific notation for 1,200,000. It does not ask whether the string is a whole number. It asks whether the string converts to any numeric type. Float, money and decimal all count.

The next query tests eighteen strings. It shows what ISNUMERIC says, and what a conversion to int and to decimal says. The last column is a guard that this post builds in a later section. This needs no database, so there is nothing to clean up.

SELECT v.Txt,
       ISNUMERIC(v.Txt) AS IsNumericResult,
       TRY_CAST(v.Txt AS int) AS AsInt,
       TRY_CAST(v.Txt AS decimal(18,4)) AS AsDecimal,
       CASE WHEN TRY_CAST(v.Txt AS int) IS NOT NULL AND v.Txt LIKE N'%[0-9]%' THEN 1 ELSE 0 END AS SafeInt
FROM (VALUES (N'12.45'), (N'12e5'), (N'1d2'), (N'$'), (N'+'), (N'-'), (N'.'), (N','), (N'1,234'),
             (N'$15'), (N' 7 '), (N'abc'), (N''), (N'0x1A'), (N'12-'), (N'(5)'), (N'€5'), (N'42')) AS v(Txt);

On this test server (us_english), the ISNUMERIC function returns 1 for thirteen of the eighteen strings. Only two of the thirteen are whole numbers, 42 and the padded 7. Nine return NULL as an int, and two more convert to 0. The strings 12e5 and 1d2 are floats. The dollar sign and the euro sign are money. A lone plus, minus, period or comma passes because money accepts them. Money also accepts a comma as a thousands separator, so 1,234 passes.

Test the Type You Will Use

The fix is to test the exact type you plan to convert to. TRY_CAST tries the conversion and returns NULL when it fails. It needs SQL Server 2012. For a whole number, ask for int. For an amount, ask for decimal with the scale you need. A second query shows how the same strings behave under other target types.

SELECT TRY_CAST(N'12e5' AS float) AS E5AsFloat,
       TRY_CAST(N'1d2' AS float) AS D2AsFloat,
       TRY_CAST(N'$15' AS money) AS DollarAsMoney,
       TRY_PARSE(N'1,234' AS int) AS ParseComma,
       TRY_CAST(N'1,234' AS int) AS CastComma;

So 12e5 is a float, and it equals 1,200,000. The letter d works as an exponent too, and 1d2 equals 100. The dollar string is money. TRY_PARSE reads the comma in 1,234 as a thousands separator, while TRY_CAST refuses it.

TRY_CAST has quirks of its own. Look at the int column again. A plus sign, a minus sign and an empty string all convert to 0. A bare sign is not a number, and an empty string is not zero. Add a guard that demands at least one digit. The column SafeInt does that with a LIKE pattern. It passes only 42 and the padded 7, and TRY_CAST allows spaces around a number.

Two Older Tricks

Two older tricks work around the ISNUMERIC function. The first appends N’.0e0′ to the string before the test. A whole number becomes a valid float, and a string that already has an e or a period does not. It works only for whole numbers, and it has a blind spot. An empty string gets through. The second trick uses PATINDEX to find the first character that is not a digit. A result of 0 means all digits, and the same blind spot appears.

SELECT ISNUMERIC(N'12e3' + N'.0e0') AS WithE, ISNUMERIC(N'12345' + N'.0e0') AS Digits, ISNUMERIC(N'12.45' + N'.0e0') AS Decimal2, ISNUMERIC(N'$15' + N'.0e0') AS Dollar, ISNUMERIC(N'' + N'.0e0') AS Blank;
SELECT PATINDEX(N'%[^0-9]%', N'12345') AS Digits, PATINDEX(N'%[^0-9]%', N'12e5') AS WithE, PATINDEX(N'%[^0-9]%', N'') AS Blank;

The first trick rejects 12e3 and $15. It also rejects 12.45, which is a valid decimal. It accepts the empty string. PATINDEX returns 0 for 12345, 3 for 12e5, and 0 for the empty string again. Either trick needs a separate check for a blank. TRY_CAST with the digit guard needs none.

One more idea adds nothing. Wrapping TRY_PARSE in ISNUMERIC changes no result. TRY_PARSE already returns NULL on failure, and ISNUMERIC of NULL is 0. Test the TRY_PARSE result for NULL instead.

What Each Test Costs

The tests differ in speed. This script builds 200,000 numeric strings and times three of them. TRY_PARSE depends on the common language runtime, and it pays for that.

DROP TABLE IF EXISTS #Nums;
SELECT TOP (200000) CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS nvarchar(20)) AS Txt
INTO #Nums
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;
DECLARE @t0 datetime2, @x int, @isnum int, @cast int, @parse int;
SET @t0 = SYSDATETIME(); SELECT @x = COUNT(*) FROM #Nums WHERE ISNUMERIC(Txt) = 1; SET @isnum = DATEDIFF(MILLISECOND, @t0, SYSDATETIME());
SET @t0 = SYSDATETIME(); SELECT @x = COUNT(*) FROM #Nums WHERE TRY_CAST(Txt AS int) IS NOT NULL; SET @cast = DATEDIFF(MILLISECOND, @t0, SYSDATETIME());
SET @t0 = SYSDATETIME(); SELECT @x = COUNT(*) FROM #Nums WHERE TRY_PARSE(Txt AS int) IS NOT NULL; SET @parse = DATEDIFF(MILLISECOND, @t0, SYSDATETIME());
SELECT @isnum AS IsNumericMs, @cast AS TryCastMs, @parse AS TryParseMs;
DROP TABLE #Nums;

Your milliseconds will differ. TRY_CAST was the fastest and TRY_PARSE the slowest, by a wide margin. Use TRY_PARSE only when culture rules matter, such as a thousands separator or a currency sign you want to accept.

Is ISNUMERIC Useless?

You could argue that the ISNUMERIC function returns 1 for too many strings to be worth using, given such results. That goes too far. ISNUMERIC is fine when the question is whether a string could be some kind of number. It fails when the next line converts the string to a specific type. The mistake is in the match between the test and the conversion.

What to Remember

The ISNUMERIC function returns 1 for float, money and decimal forms. Use TRY_CAST with the type you will convert to. Add a digit guard for whole numbers. Reach for TRY_PARSE only when you need culture rules. Test the type, not the idea of a number.

A string is not numeric because ISNUMERIC says so, it is numeric when it converts to the type you need.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.