Earlier I blogged about Puzzle – ISNUMERIC and Unexpected Results – SQL in Sixty Seconds #076 and it is one of the most popular blog posts. Lots of people answered the question correctly.
In the blog post I had a video puzzle. You can watch the video over here.
[youtube=http://www.youtube.com/watch?v=nGH1bv-qmBI]
Now the puzzle was what was the value which I had passed in the second select statement which was returning the value of the SELECT statement as 1. The answer is I have passed TAB in the ISNUMERIC function.
The reason given by many of you is that as an ASCII value of the TAB is 9, it is returning the 1 in the ISNUMERIC function. Well, I am personally not sure if that is the valid reason or not. I will let you all decide the answer for the same.
However, MSDN’s official documentation says – ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. That explains that it works with more than one data type.
If any of the blog readers have more details about the same, would you please post it in a comment and I will update the blog post with reference.
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
https://docs.microsoft.com/en-us/collaborate/connect-redirect
In this page, it is written in comments that “select convert(money, char(9)) does successfully return 0.0000, therefore ISNUMERIC(CHAR(9)) returns 1”
But, I am not agree with this one, because select convert(money, char(32)) also returns 0.00
It is also written there, “String to money conversion ignores all white spaces, including tab”
I have checked with following statements,
select ISNUMERIC(‘1 ‘)
select ISNUMERIC(‘1 ‘)
This two statements returns 1 & 0 respectively.
In first, there is space and in second, there is tab.
I think, It is ignoring white space, so ISNUMERIC(‘ ‘) returns 0 , while ISNUMERIC(‘1 ‘) returns 1.
And Numeric column can be delimited by TAB, so ISNUMERIC(‘ ‘) returns 1, while ISNUMERIC(‘1 ‘) returns 0.
Note: ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).
SELECT ISNUMERIC(‘ ‘) tab, ISNUMERIC(‘+’) plus, ISNUMERIC(‘-‘) minus, ISNUMERIC(‘$’) dollar
select ISNUMERIC(‘1 ‘) onetab, ISNUMERIC(‘ 1’) tabone
In second statement we can see number preceded by tab (‘tabone’ column) is also numeric.
I think this is numeric because we are using +,-,$ and horizontal TAB before number to maintain right alignment.
but I am not sure about it.
The url
have some thing about isnumeric.
by the way, select isnumeric(CHAR(13)) also return 1.
I think we looked too much at other people’s responses and thought too narrow. We all just wanted the free 1 month membership. But, after doing some research, I found the correct answer at: which does a very good job explaining it.
There are reasons why particular characters are considered numeric by ISNUMERIC such as tabs, currency signs, carriage returns, or other special characters. These characters are presumed to come from raw data, so they may act as a delimiter, the end of a number, or even an enumeration of a number. For the case of a tab,
it is because a column of numbers is frequently delimited by a tab.
If you want to check to see if a value is a number, then you should do so using other means.
First one is a space character and second one is ASCII # 255 which is a numeric value and the trick is it looks like a space! ;)
Is it your understanding that “since tab space expression will return the ASCII code 9, ISNUMERIC(tab space) is equivalent of executing ISNUMERIC(9) which results 1.”? If that is so, ISNUMERIC of any character should return a 1 because every character has some ASCII code value! But that is not the case and, as you can see in the article, there is a certain set of characters that ISNUMERIC seems to falter by. For other characters, it will return a 0.
The way I understood it is this – ISNUMERIC is not simply testing for a string with only digits. It is testing for any string that can be converted to a valid numeric data type – which include even money type. And it seems to be lenient to support data streams with different sorts of delimiters or qualifiers that denote currency or so. Given tab is one of the often used value delimiter, ISNUMBERIC seems to not fail and returns a 1! ISNUMERIC(‘,’) will also return 1.