THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

SQL Teaser: Some Things Are Not What They Seem

This one is a little sneaky, don’t send me hate mail for it.

What does this return?



Copy and paste it into QA/SSMS to verify  :-0



Published Friday, April 25, 2008 10:46 AM by Denis Gobo
Filed under:



unclebiguns said:

Wow, I really expected 1, 1 or if one would return 0, I would have expected it to be the '+' as you rarely use the sign for positive values. Especially since IsNumeric('.') has gotten me before.

April 25, 2008 10:55 AM

unclebiguns said:

I looked it up in BOL and it states:

"  ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see Using Monetary Data."

April 25, 2008 10:59 AM

Scott R. said:

The IsNumeric function returns 1 for string value numbers with either leading sign (+ or -) or no sign (including +0 and -0, even though 0 can never be negative technically), but returns 0 for string value numbers with a trailing sign ('1-' or '1+').

Building on the earlier post, I would think that the IsNumeric function should return 0 for all 3 single character values of '+', '-', and '.' (with no other characters), since the string value is missing an actual number part (any digits 0-9).  Just my opinion.

April 25, 2008 12:19 PM

Denis Gobo said:

Like I said, it is not what it seems, copy and paste the select statement into QA/SSMS and run it..surprised?

April 25, 2008 12:31 PM

noeldr said:

This is really funny :D

April 25, 2008 12:45 PM

Tom Trombly said:

It's both funny and not. A more appropriate name for this function might have been isCurrency.  But still, even that wouldn't be entirely accurate. Try Select ISNUMERIC($0,1.9)

April 25, 2008 1:01 PM

Tony Bunce said:

Good one.  Very Dashing (pun intended).


April 25, 2008 1:32 PM

wtkwest said:

...that's why I started using "not not-a-number" when importing integer data....

declare @tbl table (data varchar(50))

insert into @tbl values('1')

insert into @tbl values('.')

select case when data not like '%[^0-9]%' then convert(int, data) else null end from @tbl

Of course, my favorite unexpected in sql server is still LIKE:


[ascii] = case when 'abcd    ' like 'abcd' then 'same' else 'different' end

,[unicode] = case when 'abcd    ' like N'abcd' then 'same' else 'different' end

April 25, 2008 6:06 PM

Denis Gobo said:

Check out one of my friend's FAQ

April 25, 2008 6:15 PM

AaronBertrand said:

I wrote some similar stuff several years ago:

April 26, 2008 12:57 PM

Michael Zilberstein said:

Should be 1, 1; I see that it's not - solution is here:



April 27, 2008 10:07 AM

jerryhung said:

Confusing indeed

but is there performance difference between IsNumeric vs UDF?

April 28, 2008 12:20 PM

Mike Kennie said:

How about this?



May 1, 2008 11:19 AM

Denis Gobo said:

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

December 31, 2008 10:37 AM

Ram said:


     Its Really funny. No Human Being Cannot expect the Result of this statement without running in sql server.  

Because No one can't find difference between - and –

if you find isnumeric value of 1st symbol, u will get 1  

if you find isnumeric value of 2nd symbol, u will get 0

For a human eye, those are same. But for machine, those are different

ASCII 45 and ASCII 150 Respectively.

I felt this is the reason.

July 27, 2009 8:36 AM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement