Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

  • I think this "could" be solved using a CLR function that tries to cast the value to each acceptable numeric data type, but I never did CLR.

    Unfortunately you cannot do this in a T-SQL function due to the lack of error handling with Try/Catch.

    You can do this in a stored procedure, so something similar to the following would *technically* work in a row by row (cursor) scenario for smaller tables, where performance doesn't matter:

    CREATE PROC dbo.spuTestNumeric @Number VARCHAR(50), @IsNumeric INT = 0 OUTPUT

    AS

    DECLARE @Try INT = 0;

    DECLARE @Cast SQL_VARIANT;

    START:

    BEGIN TRY

    IF @Try<=0 BEGIN SET @Cast =CAST(@Number AS int); SET @IsNumeric=1; END

    IF @Try<=1 BEGIN SET @Cast =CAST(@Number AS numeric); SET @IsNumeric=1; END

    IF @Try<=2 BEGIN SET @Cast =CAST(@Number AS bigint); SET @IsNumeric=1; END

    IF @Try<=3 BEGIN SET @Cast =CAST(@Number AS money); SET @IsNumeric=1; END

    IF @Try<=4 BEGIN SET @Cast =CAST(@Number AS smallint); SET @IsNumeric=1; END

    IF @Try<=5 BEGIN SET @Cast =CAST(@Number AS smallmoney); SET @IsNumeric=1; END

    IF @Try<=6 BEGIN SET @Cast =CAST(@Number AS tinyint); SET @IsNumeric=1; END

    IF @Try<=7 BEGIN SET @Cast =CAST(@Number AS float); SET @IsNumeric=1; END

    IF @Try<=8 BEGIN SET @Cast =CAST(@Number AS decimal); SET @IsNumeric=1; END

    IF @Try<=9 BEGIN SET @Cast =CAST(@Number AS real); SET @IsNumeric=1; END

    END TRY

    BEGIN CATCH

    SET @Try+=1;

    GOTO Start

    END CATCH

    RETURN;

    The procedure would need to be adjusted to include only the datatypes that one considers as numeric.

    Or there can be one for each data type or the datatypes could be passed as parameters. So this is just the concept itself.

    Best Regards,

    Chris Büttner

  • I tried '%[^0-9]%' and it does not seem to work.

    create table #test (string char(10))

    insert into #test values ('12334')

    insert into #test values ('abc')

    insert into #test values ('123.3')

    insert into #test values ('-123.3')

    insert into #test values ('123$')

    select * from #test where string not like '%[^0-9]%'

    Instead of returning 12334 it does not return anything. We are running SQL Server 2005 SP4.

  • pkosiavelos (9/14/2012)


    I tried '%[^0-9]%' and it does not seem to work.

    create table #test (string char(10))

    insert into #test values ('12334')

    insert into #test values ('abc')

    insert into #test values ('123.3')

    insert into #test values ('-123.3')

    insert into #test values ('123$')

    select * from #test where string not like '%[^0-9]%'

    Instead of returning 12334 it does not return anything. We are running SQL Server 2005 SP4.

    The datatype char(10) is fixed length, so the string '12334' is padded with 5 spaces to a length of 10. And since those spaces are not numeric characters, the string is rejected by the LIKE filter.

    Try using datatype varchar(10) instead. Or add an RTRIM function in the query ("... where RTRIM(string) NOT LIKE ...")


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo, many thanks... it works fine. 🙂

  • You can convert to data type money everything identified as isnumeric().

    Try this:

    [font="Courier New"]SELECT [Ascii Code] = STR(Number),

    [Ascii Character] = CHAR(Number),

    [ISNUMERIC Returns] = ISNUMERIC(CHAR(Number))

    into #temp

    FROM Master.dbo.spt_Values

    WHERE Type = 'P'

    AND Number BETWEEN 0 AND 255

    AND ISNUMERIC(CHAR(Number)) = 1

    select *,

    convert(money, [Ascii Character])

    from #temp

    where [ISNUMERIC Returns] = 1

    [/font]

  • Thanx a lott.. A new information for me.. 🙂

  • Very nice article! There is a typo:

    "164 (Yen sign)" should actually reference 165.

  • Rather than parsing the string, how about attempting to CAST the string in a TRY...CATCH block and return a pass/fail value on that basis?

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • ronmoses (9/14/2012)


    Rather than parsing the string, how about attempting to CAST the string in a TRY...CATCH block and return a pass/fail value on that basis?

    ron

    The problem here would be performance, no? Especially if you were trying to do this in-line in a sql statement (for example an update), you'd need a function?

    >L<

  • WayneS (12/1/2010)


    I have a question for clarification:

    Ascii Codes 36 (Dollar sign), 128 (Euro sign), 163 (British Pound sign), and 164 (Yen sign) are included because they are frequently used as enumerators to identify the type of number or, in this case, the currency type the number is meant to represent.

    Ascii Code 164 is a special character and is included because it is frequently used by accountants and some software to indicate a total or subtotal of some type. It is also used by some to indicate they don't know what the enumerator is.

    Is ASCII 164 the Yen (1st quote), or something else (2nd quote)? It does appear to be the Yen, and you didn't cover ASCII 165, so in the second quote should that be 165?

    Amen to both of you. I'm not so interested in "what is all digits" as I am in "what will survive a cast/convert". I see that the IsReallyNumeric codeset on http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html, pointed to by various people here, does pretty much what I want.

    I particularly appreciate that article coming at the problem from different architecture levels although my personal experience (even though I is an architect <g>) is that you'd better do it at the database level, even if your web tier standards assures you of appropriate data coming in. And this is not just belt-and-suspenders -- ask me to explain if interested!

    But I also think the function to which yuri 38568 linked (http://codecorner.galanter.net/2009/04/03/tsql-isnumeric-function-returns-false-positives/) is a pretty nice idea to resolve the original and general "is numeric" question - with my limited understanding - so does anybody else have any reason not to use this approach? Later on in the thread, clark_button appears to be doing something similar.

    Thanks to everybody in this thread, and especially to Jeff as usual!.

    >L<

  • Lisa Slater Nicholls (9/14/2012)


    ronmoses (9/14/2012)


    Rather than parsing the string, how about attempting to CAST the string in a TRY...CATCH block and return a pass/fail value on that basis?

    The problem here would be performance, no? Especially if you were trying to do this in-line in a sql statement (for example an update), you'd need a function?

    Yes, I would definitely only use something like this for small data sets. But I thought I'd toss it in there as an idea.;-)

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Lisa Slater Nicholls (9/14/2012)


    I see that the IsReallyNumeric codeset on http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html, pointed to by various people here, does pretty much what I want.

    Oh, be careful, Lisa. The "filter" built into the method of the article can discriminnate for or against just about anything you want, as well. The problem with the method used in the link you cited is that it uses a scalar function which has some pretty severe performance problems that I wouldn't even justify the use of for supposedly small data sets.

    The method in the article can use an "iSF" or "Inline Scalar Function" (for lack of a better term). Please see the following information for more on that subject.

    http://www.sqlservercentral.com/articles/T-SQL/91724/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ronmoses (9/14/2012)


    Lisa Slater Nicholls (9/14/2012)


    ronmoses (9/14/2012)


    Rather than parsing the string, how about attempting to CAST the string in a TRY...CATCH block and return a pass/fail value on that basis?

    The problem here would be performance, no? Especially if you were trying to do this in-line in a sql statement (for example an update), you'd need a function?

    Yes, I would definitely only use something like this for small data sets. But I thought I'd toss it in there as an idea.;-)

    ron

    Just as a side bar, Ron... I try to never incorporate something that will only be used for small data sets because, once it's out there, it's difficult to control how and on what something will be used for especially if someone is fighting a tight schedule. It usually takes no more effort to make something scalable.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • s b dragoo (9/14/2012)


    Great little article. Several years ago while I still was a junior DBA, I ran into this very issue. It was quite perplexing at the time (and unfortunately involved people's property tax bills). I finally figured out the error of my ways. Your article has pointed out a couple of characters that I was not aware of still. Greatly appreciated!

    You bet. Thanks for stopping by, Scott.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • josuecalvo (9/14/2012)


    You can convert to data type money everything identified as isnumeric().

    Try this:

    [font="Courier New"]SELECT [Ascii Code] = STR(Number),

    [Ascii Character] = CHAR(Number),

    [ISNUMERIC Returns] = ISNUMERIC(CHAR(Number))

    into #temp

    FROM Master.dbo.spt_Values

    WHERE Type = 'P'

    AND Number BETWEEN 0 AND 255

    AND ISNUMERIC(CHAR(Number)) = 1

    select *,

    convert(money, [Ascii Character])

    from #temp

    where [ISNUMERIC Returns] = 1

    [/font]

    Understood but I'm not sure what your point is. The purpose of the article was to show that ISNUMERIC shouldn't be used to determine if a character string is all numeric digits. The MONEY conversion will all for commas, decimal points, dollar signss, and other things that aren't numeric digits.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 106 through 120 (of 168 total)

You must be logged in to reply to this topic. Login to reply