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.

Solarwinds

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)

Solarwinds
, ,
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(‘ ‘) if u r space using Tab key then u got Output 0 but u r use Tab key for Space then u got ans 1

    Reply
  • Hi Pinal,

    2 possibilities here.
    1. tab character which return value 1
    2. ascii null character which is possible to add via alt + 255 numer.

    select ISNUMERIC(‘ ‘)
    select ISNUMERIC(‘ ‘)
    select ISNUMERIC(‘ ‘)

    check this query…

    Reply
  • select ISNUMERIC(CHAR(9))

    this is also giving value as 1
    TAB key ASCII value is 9
    the above query is equivalent to select ISNUMERIC(‘ ‘)

    Reply
  • The invisible characters are:
    First Statement: ‘ ‘ is Space
    &
    Second Statement: ‘ ‘ is Tab

    Reply
  • nice

    Reply
  • Swati Singhal
    June 11, 2015 1:28 pm

    Hi Dave,

    Yes this can be ‘Tab’ but I still am unsure of the reason why it gives 1. The replies above are saying because we can succesfully convert ‘tab’ to money type.

    Select cast(char(9) as money) — gives 0.00

    But so does this

    Select cast(char(32) as money) –gives 0.00

    where char(32) is of course a ‘space’. But still ISNUMERIC(char(32)) gives 0 while ISNUMRERIC(char(9)) gives 1.

    Reply
  • Thirmal Sangisetti
    March 18, 2016 7:15 pm

    –Here you used space that ASCII Value IS 32 So it’s giving “0”

    SELECT ISNUMERIC(‘ ‘) —This is SPACE

    SELECT ‘THIRMAL’+CHAR(32)+’SANGISETTI’ –This is SPACE ASCII

    –O/P: ‘THIRMAL SANGISETTI’

    –Second one is TAB, ASCII Value is 9 it’s giving 1
    SELECT ISNUMERIC(‘ ‘)
    SELECT ‘THIRMAL’+CHAR(9)+’SANGISETTI’
    –O/P: ‘THIRMAL SANGISETTI’

    Reply
  • rickwillemain
    July 28, 2016 6:08 pm

    any non-visible character that implicitly converts to numeric

    Reply

Leave a Reply

Menu