Madhivanan's TSQL Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

sql_server	217
t-sql	211
tsql	116
sqlserver	96
BRH	78
#SQLServer	66
#TSQL	56
SQL Server	34
function	11
SSMS	9

Archive · View All

August 2007	17
August 2010	8
June 2012	7
June 2011	7
November 2007	7
August 2012	6
May 2012	6
November 2011	6
August 2011	6
October 2011	6

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (421) · Preferences

Understanding how ISNUMERIC function works

Jan 24 2014 12:00AM by Madhivanan

Often I hear from people that ISNUMERIC function is not working properly and there is a bug. One example people refer is that both of ISNUMERIC('12d2') and ISNUMERIC (',') return the value 1.

Well. The definition of ISNUMERIC function is Determines whether an expression is a valid numeric type. So any expression that can be converted to numeric type is valid for ISNUMERIC to return the result as 1.

SELECT ISNUMERIC('12D2')

The result is 1. Now we can validate this using the following expression

SELECT CAST('12D2' AS FLOAT)

The result is 1200. So here the number is expressed in scientific notation and D denotes as decimal and is evaluated as 12*100

Consider another example

SELECT ISNUMERIC(',')

The result is 1. It is because comma can be converted to MONEY datatype which can accept formatted numbers.

SELECT CAST(',' AS MONEY)

The result is 0.00

So next time when you see ISNUMERIC returns 1 for an expression, it means that expression is valid for being converted to any of the numeric datatypes listed here.

Tags:

Previous: Question of the month January 2014 - How does invalid alias work in INSERT statement?
Next: Find out where the backup file is stored

Madhivanan

2 · 40% · 12993

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

3 Comments

With SQL Server 2012 you can also use TRYPARSE() instead of ISNUMERIC(), check here:http://sqlwithmanoj.wordpress.com/2013/09/20/use-new-try_parse-instead-of-isnumeric-sql-server-2012/

commented on Jan 24 2014 12:31AM

Manoj

240 · 1% · 188

Also see Floating point formats in T-SQL

commented on Jan 24 2014 2:29AM

visakhm

186 · 1% · 253

Thanks Manoj and Visakh

commented on Jan 24 2014 2:56AM

Madhivanan

2 · 40% · 12993

Your Comment

Sign Up or Login to post a comment.

"Understanding how ISNUMERIC function works" rated 5 out of 5 by 5 readers +Madhivanan