A Little About Why ISNUMERIC Sucks In SQL Server

Posted on by Erik Darling

A Little About Why ISNUMERIC Sucks In SQL Server

Video Summary

In this video, I delve into why the `ISNUMERIC` function in SQL Server is problematic and why it’s best to avoid using it. I illustrate how `ISNUMERIC` can incorrectly identify non-numeric characters as numeric, leading to potential data integrity issues. Additionally, I discuss my frustration with the `TRY_CAST` and `TRY_CONVERT` functions, particularly their inconsistent behavior when dealing with certain data types like unique identifiers. To address these challenges, I provide a regex-based approach using `NOT LIKE` to filter out values that contain non-numeric characters, ensuring more reliable results in your queries.

Full Transcript

Erik Darling here with Erik Darling Data, Data, Data, Data, or just Darling Data, Data, or just Erik Darling. We’re going to talk about why isNumeric sucks and why you shouldn’t use it and alternatives you have to using it. And I’m going to sneak in also why I get annoyed with the TRICAST function in some circumstances.

So, let’s start. Let us begin. Gather around, children, by putting all of these things into a table. A temp table, because I am staunchly anti-table variable. All right. So, we have a dollar sign, a comma, a period, a one with some commas, a dash, a plus sign, whatever car 9, 10, 11, 12, and 13 is, I sort of forget.

And let’s look at the output of this. Now, the isNumeric function is dead certain that all of these things are numeric. Not numeric, darling. Just numeric.

So, a dollar sign is numeric. A comma is numeric. A period is numeric. A one with two commas around it is numeric. A minus sign is numeric. A plus sign is numeric. And whatever the heck these blank ASCII characters are, are also numeric.

Now, where TRICAST falls a little bit short on us, and… Well, I mean not short. It’s not wrong. It’s just… So, like, I get it. Like, if it returns a null if it can’t convert something.

But then it returns a zero when it can. And that can lead to some confusing logic for some folks out there who are somewhat dimwitted, like yours truly. I don’t know. That’s about it.

At least right now. But, uh, if you use a sort of regex-ish expression, you will get what I believe correctly are zeros all down this line here. And I always have to look this up whenever I need to do it.

Except now I get to look it up on my blog because I have a post about it and I have this video about it. But if you say where T dot N, in this case, because my table is aliased correctly as T, and the column’s name is N. If you say where that is not like this expression right here.

Alright, this string. Percent, open bracket, caret, zero through nine, close bracket, percent. This will tell you if there are any characters in a row, well, in a particular, uh, you know, column row, that, uh, that are not the numbers zero through nine.

And I know this logic is a little bit obtuse and feels a little bit backwards, but that’s, that’s what we’re looking for here. So, that’s good. Alright, we can return consistent zeros for things that are not the numbers zero through nine, which are probably the things that we care about the most.

Um, if you wanted to make allowances for, uh, dots and commas, you could do that, uh, in here as well. But that is totally up to you. Now, where things get annoying for me with TriCast, and TriConvert, it’ll do the same thing for both of these.

What we’re looking for is, is the whole point of, can you try to do this for me? Is that you expect to get a null back if you can’t do it. If you try and fail, you should get back a null.

That doesn’t always happen though. Now, let’s take for instance, we do this. Explicit conversion from data type unique identifier to int is not allowed.

Then return a null. Give me a null. Give me a null. I know it can’t be done. I want a null back.

Now, this is a not terribly common occurrence. I agree. But, um, there are, uh, at least various points in SQL Server’s DMVs where, uh, there are SQL variant columns.

And those SQL variant columns, of course, are, uh, you use that data type, that horrible mistake of a data type. Because, they have all sorts of different things in them. Some of those things are GUIDs.

Some of those things are numbers. Some of those things are strings. Uh, there’s just all sorts of crap jammed into them. And if you want to try to cast that column as something else, or if you want to try to filter on where, uh, you know, try cast to something sends back, uh, a not null value.

Like, where try convert, try cast to try convert something is not null. Which, you know, again, isn’t the greatest idea query-wise in the world. But if you’re querying system DMVs, who cares really?

You can throw all the crap at those you want. And it’ll probably turn out just about the same. Because the code behind them is absolutely wackadoodles. So anyway. Don’t use this numeric.

Uh, try convert, try cast can be tricky. Uh, they can lead to some weird. Uh, logic hoop jumping that you may want to do.

And, uh, what I find to be the most reliable thing to do is use not like, and, uh, again, this expression right here to, uh, filter out rows that can, uh, values that contain things that are not the number 0 through 9.

If you’re aware of any numbers that, uh, are not, that are outside the bounds of 0 through 9 that could be used in here. I don’t mean 11, cause 11 is two ones and all that stuff.

Uh, then please let me know, cause, uh, I would like to patent them as soon as possible. Cool. Alright.

That’s good. Thank you for watching. Hope you enjoyed yourselves. I hope you learned something. I hope that you have been enlightened. I hope that you have had quite the epiphany watching this video. Uh, if you, if you feel enlightened and epiphanized, uh, please like and subscribe.

Uh, I’ll give you a dollar if you do it in the next 30 seconds, starting now. Uh, 30 seconds is up. Sorry about that.

I move fast around here. Alright, cool. Uh, thank you again for watching. You are beautiful.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Related Posts

4 thoughts on “A Little About Why ISNUMERIC Sucks In SQL Server”

  1. I think your regex based solution fails on simple cases. For example, you’re trying to see if the test value is a bigint. -1 is a valid bigint but your code flags it as not valid. Would `try_cast(value as DT) is not null` suffice to get around “maybe it’s null, maybe it’s 0”?

  2. Im a little dim witted myself but I use this (below). Sadly i deal with some older systems that insist on leading zeros in numbers as strings in tables, so the regex alone wont do the trick.

    DROP TABLE #numerictest
    ;
    CREATE TABLE #NumericTest (id int IDENTITY(1,1) not null, string varchar(25))
    ;

    INSERT INTO #NumericTest (string) VALUES
    (‘0123’),
    (‘3245’),
    (‘thiswillfail’),
    (‘justlikeYOU23’),
    (‘34567’+CHAR(13))
    ;

    SELECT
    nt.id
    , nt.string
    , ‘IsNumeric’ =
    CASE
    WHEN SUM
    (
    CASE
    WHEN z.value NOT LIKE ‘%[^0-9]%’ THEN 0
    ELSE 1
    END
    ) > 0
    OR
    LEFT(string,1)=’0′ THEN 0
    ELSE 1
    END
    FROM
    #NumericTest nt
    OUTER APPLY STRING_SPLIT(CAST(CAST(CAST
    (nt.string AS NVARCHAR) AS VARBINARY) AS VARCHAR), CHAR(0)) z
    WHERE
    z.value ”
    GROUP BY
    nt.id
    , nt.string

Comments are closed.