Efficient ISNUMERIC() replacements on SQL Server?

Question

So I just spent 5 hours troubleshooting a problem which turned out to be due not only to the old unreliable ISNUMERIC but it looks like my problem only appears when the UDF in which ISNUMERIC is declared WITH SCHEMABINDING and is called within a stored proc (I've got a lot of work to do to distill it down into a test case, but my first need is to replace it with something reliable).

Any recommendations on good, efficient replacements for ISNUMERIC(). Obviously there really need to be variations for int, money, etc., but what are people using (preferably in T-SQL, because on this project, I'm restricted to SQL Server because this is a high-volume SQL Server to SQL Server data processing task)?

Sible

I have not been able to try it, but given my experience with EXTREMELY poor performing SQL UDFs (admittedly called for millions of rows), I'm inclined to give the .NET CLR a try and have awarded the bounty to that answer. – Sible

So you awarded the bounty to an answer that was given back in November? Seems like the bounty was wasted then, no? – Coricoriaceous

I was hoping that the bounty would spur someone to post a great solution that no one had thought of, yet I didn't think wasting half the bounty would be good. – Sible

Well, I thought my extended stored procedure rocked! :) Hehehe...j/k. – Coricoriaceous

Answer 1

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

Immigrate

@le dorfier: Is it? I would expect this to be faster than anything but the most trivial T-SQL expressions. But I have no numbers, it's a gut feeling only. Can you point to a resource that has some numbers? – Ternion

Note because this showed up on the front page in 2015: As of SQL Server 2012 you can use TRY_CAST() or TRY_CONVERT(). – Lenee

@BaconBits: I did not know this. Thank you! Edited! – Immigrate

Answer 2

Depending on the circumstances and the performance characteristics of the validation, I sometimes use a variation of the LIKE expression instead. For example:

NOT LIKE '%[^0-9]%'

Note that this specific example is fairly naive. It doesn't guarantee that the value is valid for conversion to a particular data type. It also doesn't allow for +/- signs or decimal points if you need those.

Verditer

This might actually be closest to what the original logic does in the system we are replacing. – Sible

The asker stated this is a high volume task, to which NOT LIKE will definitely not scale well. – Cleanshaven

IsNumeric doesn't scale well either, and nor will writing your own UDF. This is inherently going to be a relatively slow check. – Verditer

Answer 3

Another option might be to write an extended stored procedure in a language such as C, make it into a DLL and make it available to SQL Server.

I don't imagine it would take too many lines of code to do, and it probably would be faster than writing a Managed Stored Procedure in .NET, because you wouldn't incur the extra overheard from loading the CLR.

Here's a tidbit of information: http://msdn.microsoft.com/en-us/library/ms175200.aspx

Here's some C++ code that might work for you:

using namespace std;

int checkNumber() {
  int number = 0;
  cin >> number;
  cin.ignore(numeric_limits<int>::max(), '\n');

  if (!cin || cin.gcount() != 1)
    cout << "Not a number.";
  else
    cout << "Your entered: " << number;
  return 0;
}

Coricoriaceous

Answer 4

Generally as a practice, I try not to let untyped data into the database, as it is better more suited to either handle it at the application layer, or for batch imports handle it in SQL Integration Services so that the data comes in typed correctly from the start.

I have had to do it many times in the past and usually the fastest way is to write your own user defined function to verify the data is in the format you expect, as most of the time the overhead to calling out to an extended stored proc or managed code for simple validation is slower than just doing it in T-SQL.

Sfumato

Yes, unfortunately, the business logic I have to duplicate on these (freeform columns) has not been applied at the ETLlevel of the datawarehouse and I have no choice. – Sible

Answer 5

Following the .NET CLR route, you could go with a regular expression, particularly if you expect a certain range of numbers.

SQL 2005 and Regular Expressions

Advisee

Broken link. and I thought RegExp did not work on 2005-2008 – Mckean

Answer 6

According to Microsoft support the only efficient way to replace UDF function is to write your own .NET function version.

Of course, if your database admin allows that :).

Mine doesn't :(.

Ieyasu

Answer 7

Are you ever going to be handling number systems outside of your own (human) language, like Chinese etc? If so, I'd suggest using the libuninum library.

Hole

I could use things outside T-SQL, I think I would just use a CLR Parse function with a defined format. – Sible

Answer 8

How about implementing these two functions:

CREATE FUNCTION dbo.isReallyNumeric  
(  
    @num VARCHAR(64)  
)  
RETURNS BIT  
BEGIN  
    IF LEFT(@num, 1) = '-'  
        SET @num = SUBSTRING(@num, 2, LEN(@num))  

    DECLARE @pos TINYINT  

    SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))  

    RETURN CASE  
    WHEN PATINDEX('%[^0-9.-]%', @num) = 0  
        AND @num NOT IN ('.', '-', '+', '^') 
        AND LEN(@num)>0  
        AND @num NOT LIKE '%-%' 
        AND  
        (  
            ((@pos = LEN(@num)+1)  
            OR @pos = CHARINDEX('.', @num))  
        )  
    THEN  
        1  
    ELSE  
    0  
    END  
END  
GO  

CREATE FUNCTION dbo.isReallyInteger  
(  
    @num VARCHAR(64)  
)  
RETURNS BIT  
BEGIN  
    IF LEFT(@num, 1) = '-'  
        SET @num = SUBSTRING(@num, 2, LEN(@num))  

    RETURN CASE  
    WHEN PATINDEX('%[^0-9-]%', @num) = 0  
        AND CHARINDEX('-', @num) <= 1  
        AND @num NOT IN ('.', '-', '+', '^') 
        AND LEN(@num)>0  
        AND @num NOT LIKE '%-%' 
    THEN  
        1  
    ELSE  
        0  
    END  
END  
GO

Original Source

Armament

Answer 9

For SQL Server 2005 and above.... take advantage of try/catch...

declare @test varchar(10), @num decimal
select @test = '0123A'

begin try
    select @num = cast(@test as decimal)
    print '1'
end try 
begin catch
    print '0'
end catch

prints 0.

Change @test = '01234' or @test = '01234.5' and it prints 1.

Leeanneleeboard

So do you want me to put this in a while loop and iterate over every record I'm checking? This is incompatible with set-based operations. You know, the kind of operations that SQL is designed to do. – Mckean

Answer 10

IsNumeric() seems to have problems with spaces, 'D', 'E', dollar signs and all sorts of other characters. What we typically want is something that tells us whether a CAST or CONVERT will succeed. This UDF, while not the fastest solution, has worked very well for me.

create function dbo.udf_IsNumeric(@str varchar(50))
  returns int
as
begin
  declare @rtn int
  select @rtn =
    case
      when ltrim(rtrim(@str)) in('.', '-', '-.', '+', '+.') then 0
      when ltrim(rtrim(@str)) like '%[^-+.0-9]%' then 0
      else isnumeric(@str)
    end
  return @rtn
end

Grassgreen

I will add a tab to the line "when ltrim(rtrim(@str)) in ('.', '-', '-.', '+', '+.', CHAR(9)) then 0" Otherwise it is v. good. – Accurate

Answer 11

SQL 2012 onwards, you can use TRY_PARSE() function instead of ISNUMERIC().

SELECT
 TRY_PARSE('123' as int) as '123'
,TRY_PARSE('abc' as int) as 'abc'

Pittance