SQL SERVER – Puzzle – ISNUMERIC and Unexpected Results – SQL in Sixty Seconds #076

It has been a long time since I have asked, puzzled on this blog so let us have fun time together with ISNUMERIC function. If you get the correct answer to this question, I will give you one month free subscription to Pluralsight.

The question is in the form of video which is displayed here. Watch the video and answer correct question in the comment on the this blog. You have 48 hours from the time of publishing this blog to answer it correctly.

If you due to any reason, you can’t watch the video, here is the image taken from the video. I strongly suggest you watch this video to get a clear idea of the puzzle.

SQL SERVER - Puzzle - ISNUMERIC and Unexpected Results - SQL in Sixty Seconds #076 puzzleisnumeric

In the above image, when I execute first select statement, it returns me answer as 0 but when I execute the second function, it gives me answer as 1. What is passed in the second select statement in ISNUMERIC function which is producing answer as 1, even though it is not visible.

Please leave your answer as a comment to this blog post. Do let me know what you think about this puzzle. Based on your feedback, I will post more such puzzles.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Search Records with Single Quotes – SQL in Sixty Seconds #075
Next Post
SQL SERVER – Fixing Backup Error – Operating system error 5(Access is denied.) – SQL in Sixty Seconds #077

Related Posts

53 Comments. Leave new

  • SELECT ISNUMERIC(‘ ‘)
    SELECT ISNUMERIC(‘ ‘)

    Added Space in first select and tab in second statement

    Reply
    • SELECT ASCII(‘ ‘)– tab
      SELECT ISNUMERIC(char(9))– returns1
      /*
      SELECT CONVERT(int,char(9))– conversion fails
      SELECT CONVERT(float,char(9))–conversion fails
      SELECT CONVERT(NUMERIC,char(9))–conversion fails
      SELECT CONVERT(money,char(9))– conversion is successful
      */
      If the string value is converted to int,numeric,float or money then isnumeric() returns 1 else 0. in the above case isnumeric(‘ ‘) –tab– can be converted to money. hence it returns 1.

      Reply
  • Bug with isnumeric. Use try_parse.

    Reply
  • Adding on to the last reply, it is because most of the time, numeric columns are delimited by a tab, that why isnumeric is designed this way.

    Reply
  • abdulwaheed324
    October 29, 2014 3:06 pm

    when we have Tab as the argument between the single quotes then it will return 1.

    Reply
  • abdulwaheed324
    October 29, 2014 3:13 pm

    When we have no space or any number of space characters as an argument between the single quotes of ISNUMERIC() then it returns 0.
    When we have Tab as an argument between single quotes of ISNUMERIC() then it returns 1.

    Reply
  • tab in second one:)

    Reply
  • Hi Pinal,
    Select isnumeric(‘ ‘)
    it will be tab
    Ascii value= char(9)

    Reply
  • Tab in the second select statement

    Reply
  • Joe Gakenheimer
    October 29, 2014 5:36 pm

    Obviously you put in an ASCII character such as a tab, carriage return, linefeed, or some character along those lines. Those are ASCII characters and ASCII characters are converted to integers in SQL Server; such as:

    SELECT ASCII(”) — return null
    SELECT ASCII(‘ ‘) — returns 9

    Reply
  • Passing tab value in second select statement its giving result 1
    For example:

    Select Len(‘ ‘)

    Select Len (‘ ‘)

    This gives the same result

    Reply
  • Pressed Tab to get result 1.

    Reply
  • It’s a Tab….

    Reply
  • Harish Kalchare
    October 29, 2014 6:29 pm

    The first select returns 0 because space is passes as expression to the ISNUMERIC function, the second select returns 1 because tab is passes as expression to ISNUMERIC function. The ISNUMERIC function returns 1 if the input expression evaluates to a valid numeric data type. The tab character is evaluated as money data type. SELECT CAST(‘ ‘ as money) converts tab character to money data type.

    Reply
  • Rakesh Manchikanti
    October 29, 2014 6:41 pm

    it should be TAB key for second one..

    Reply
  • second on is TAB

    Reply
  • The invisible tab character returns 1 from ISNUMERIC, as do some other characters that are not traditionally thought of as numbers.

    Reply
  • As others have pointed out, the second ISNUMERIC statement contains a tab character. But why does that allow IsNumeric to return 1? The reason is that, by definition, “ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type.” Two of those numeric types are money and smallmoney.

    The money data types allow certain special characters. For example run this code:

    DECLARE @m CHAR(1)
    SET @m = CHAR(9)
    SELECT CAST(@m AS MONEY)

    The result is: 0.00

    So for the money data type, the tab character evaluates to a valid numeric data type, and thus ISNUMERIC() returns 1.

    You can see the same behavior with SELECT ISNUMERIC(‘$’)

    Reply
  • Hi Pinal,
    In the second select statement you have passed Tab character.

    Description of the ISNUMERIC function as-
    “Determines whether an expression is a valid numeric type.”
    ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.”
    –uses values 0-255 from the undocumented spt_Values table instead of a loop from 0-255
    SELECT [Ascii Code] = STR(Number),[Ascii Character] = CHAR(Number),
    [ISNUMERIC Returns] = ISNUMERIC(CHAR(Number))
    FROM Master.dbo.spt_Values WHERE Type = ‘P’ AND Number = 9 AND ISNUMERIC(CHAR(Number)) = 1

    Ascii Code 9 is a TAB character and is included because a column of numbers is frequently delimited by a TAB.

    Reply
  • Hi Pinal Dave,

    The values is TAB. It gives the result 1.

    Regards,
    Pankaj

    Reply
  • First select statement is having one SPACE
    Second one is haivng one TAB … TAB is nothing but CHAR(9)

    Reason behind that:
    ISNUMERIC returns 1 if the string data can be converted to any one of int, numeric, money, and float. In this case, select convert(money, char(9)) does succesfully return 0.0000, therefore ISNUMERIC(CHAR(9)) returns 1.

    Reply

Leave a Reply

Menu