THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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?

 

SELECT ISNUMERIC('+'),ISNUMERIC('–')

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

 

 

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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).

SELECT ISNUMERIC('+') + ISNUMERIC('–') + ISNUMERIC('-') = 2

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:

select

[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

http://www.tek-tips.com/faqs.cfm?fid=6423

April 25, 2008 6:15 PM
 

AaronBertrand said:

I wrote some similar stuff several years ago:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

April 26, 2008 12:57 PM
 

Michael Zilberstein said:

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

SELECT ASCII('–'), ASCII('-')

:-)))

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?

SELECT

ISNUMERIC('+'),ISNUMERIC('-')

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:

Denis,

     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.

http://archive.gingerall.cz/archives/public/sablot2001/msg00960.html

July 27, 2009 8:36 AM

Leave a Comment

(required) 
(required) 
Submit

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 http://sqlservercode.blogspot.com/ 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

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement