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.
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)
53 Comments. Leave new
Pinal don’t have an answer but I am intrigued by this function.
Why does ISNUMERIC(‘9D5’) OR ISNUMERIC(‘9E5’) RETURN 1 WHILST
ISNUMBER(‘9F5’) RETURN 0 here F can be replaced by any other letter.
I am watching this space to improve my understanding about the function. Surely this is not a bug and I am missing something. :)
Ammet, the reason why “9E5” is numeric is because that’s scientific notation for 9*10^5. If you run the query SELECT 9e5, you will get 900,000. I didn’t know that until I just tried it.
tab character
Hello Sir,
I haven’t watch the Video , but what i understood these are the some answers what i got
Here we will pass dot, $, +,- ans comma
that means select (‘.’) , select ISNUMERIC(‘$’) ,select ISNUMERIC(‘+’), select ISNUMERIC(‘-‘),select ISNUMERIC(‘,’) then it will return 1.
If explanation is also required then plese notify me.
Regards,
manifbest
Hi Pinal,
In the second statement,we have a character whose ASCII value is 160,which is not visible.
declare @a varchar(10)
select @a=char(160)
select @a,isnumeric(@a)
tab in second one
In the second select statement pass the tab that’s we got one.
Hello Pinal,
find my answers below:
–Space is given as input to the isnumeric function
select isnumeric(‘ ‘)
–TabSpace is given as input to the isnumeric function
select isnumeric(‘ ‘)
SELECT ASCII(‘ ‘) ASCII OF TAB RETURNS INTEGER VALUE THAT’S WE GOT 0ne
It is an ascii character 255
is you write like Select ISNUMERIC(‘Â ‘) and type 255 by pressing ALT button it will give output 1
The character is from Extended ASCII character set and stands for non breaking space , these characters are not available on keyboard and can be printed using ALT key and decimal value its character code 255. Hence if we used it in with ISNUMERIC it gives 1
Is this TAB character?
Hello Pinal, In the second select statement an invisible character is passed i.e. ALT+255 (nbsp – Non-breaking space or no-break space )
Hi Sir,
Tab has been passed to the second query.
Thanks and ragards
Vedprakash Sharma
In the second select statement you added a tab between the single quotes to give the answer of 1 in the Results tab.
Example: SELECT ISNUMERIC(”)
We pass a Tab space in the argument will give us result 1
In second statement you have passed TAB(horizontal tab) & its ascii is 9,
but why this shows isnumeric = 1?
In second query, there is TAB. So, it is returning 1.
If we press tab in the blank space then it will get the value as 1.
select ISNUMERIC(‘space’) –0
select ISNUMERIC(‘tab’) –1
Hi Pinal, it is the TAB key. Not sure why though
First run ‘SELECT CHAR(160)’ query and then copy+paste it’s output in between IsNumeric single quote. That’s it.
SELECT CHAR(160)
SELECT ISNUMERIC(‘Â ‘)