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
SELECT ISNUMERIC(‘ ‘)
SELECT ISNUMERIC(‘ ‘)
Added Space in first select and tab in second statement
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.
Bug with isnumeric. Use try_parse.
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.
when we have Tab as the argument between the single quotes then it will return 1.
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.
tab in second one:)
Hi Pinal,
Select isnumeric(‘ ‘)
it will be tab
Ascii value= char(9)
Tab in the second select statement
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
Passing tab value in second select statement its giving result 1
For example:
Select Len(‘ ‘)
Select Len (‘ ‘)
This gives the same result
Pressed Tab to get result 1.
It’s a Tab….
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.
it should be TAB key for second one..
second on is TAB
The invisible tab character returns 1 from ISNUMERIC, as do some other characters that are not traditionally thought of as numbers.
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(‘$’)
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.
Hi Pinal Dave,
The values is TAB. It gives the result 1.
Regards,
Pankaj
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.