John Magnabosco

SQL Server Development and Data Security

The Hidden Aspects of ISNUMERIC

Published Thursday, June 11, 2009 5:51 AM

I was recently strolling through some of my old blog entries from my pre-Simple-Talk days for inspiration. I ran across an entry on the ISNUMERIC function that I thought that worth re-posting. Enjoy!

SQL Server 2005 Books Online describes the ISNUMERIC function as: "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 indicates that expression can be converted to at least one of the numeric types."

To illustrate this, the following statement will return the value of 1 (true):

SELECT ISNUMERIC('486')

While the following statement will return the value of 0 (false):

SELECT ISNUMERIC('ABC')

There are some additional characters that return a positive response to the function that are not regularly considered numeric such as the dollar sign ("$"), the comma (",") and the period ("."). This occurs since they can be converted into other numeric data types. For example, the following statement will return the value of 1 (true) because it can be converted into a money data type:

SELECT ISNUMERIC('$4,860.00')

A strange thing happens when you introduce combinations of numbers and letters. Majority of the letters in the alphabet will return a 0 (false) when examined with the ISNUMERIC function; but when the letter "D" and "E" are introduced a 1 (true) is returned. On the surface, this occurrence may appear to be a bug, or inaccuracy of the function; but it is not.

Let's begin to explain this odd behavior by introducing scientific notation. Scientific notation is a method often used by Engineers, Mathematicians and Scientists to represent very large numbers in a more convenient and easier to read format. Without going into the detailed mathematician-speak regarding this notation please allow the following example to illustrate how scientific notation works: The number of 4,342,000,000,000,000,000,000 when scribed using scientific notation would appear as 4.342 x1021.

Working with scientific notation can be challenging for us Developers who must use a standard QWERTY keyboard to code our applications. Not to mention those who must document such numeric values in spreadsheets and scientific articles; thus, exponential notation was introduced to save the day.

A very simplified description of exponential notation is the representation of x10n with the letter of "E" and followed by the exponent and a character to designate positive and negative numbers. For example: 4.342 x1021 would be represented as 4.342E+21.

When programming those mega-numbers in SQL Server, and many other languages, using exponential notation returns a 32 bit signed single precision floating-point data type (also known simply as "single"). This means that if you exceed the range of -3.40E +38 through 3.40E +38, either truncation or error in conversion will occur. Try the following to illustrate the limitations of the single data type:

SELECT CONVERT(FLOAT(24),'3.40E+39')

The conversion to FLOAT(24), which is also known as a "real" number as well as a single data type, is too small to contain -3.40E +39 and returns the following error:

Arithmetic overflow error converting expression to data type real.

Replacing the letter "E" with "D" will force the scientific notation to return a 64 bit signed double-precision floating-point data type (also known affectionately as "double"). The use of "D" will extend the range returned from -1.79E +308 through 1.79E +308. This is very handy when calculating the expanse of the universe, counting atoms or keeping track of the National debt. Try the following to illustrate the expanse of the double data type:

SELECT CONVERT(FLOAT(53),'1.79D+308')

The conversion to FLOAT(53), which is a double data type, returns the exponential notation that is placed in the argument without error illustrating that the data type is large enough to store the data.
Based upon the definition of scientific notation and exponential notation you might expect the following statement to return 0 (false) because it does not follow the aspect that states that the coefficient (the number that precedes the "E") should be less than 10:

SELECT ISNUMERIC('1001D1')

Since the value can still be converted into a floating point number, the ISNUMERIC function will return 1 (true). This presents a challenge if the value "1001D1" is a serial number instead of scientific notation. If the intent is to evaluate this string to determine whether it includes a nonnumeric character, the consideration of the PATINDEX function would be recommended.

With the introduction of the regular expression "[^0-9]" as an argument to PATINDEX, the location of the non-numeric character is returned. If the value of 0 is returned, the string does not include any non-numeric values. An example of this function's syntax would be:

PATINDEX('%[^0-9]%', '1001D1')

Keeping this information in mind in regard to the tricky ISNUMERIC function and how to utilize the PATINDEX function as an alternate will save you so much time that you might just need to use scientific notation to represent the minutes available in your day for other activities such as sleep.

by Johnm

Comments

 

JoshRoss said:

What I do not like about SQLs handling of the 64bit double is the following behavior:  

-- this works as expected
select 123e10

-- this does not, it parses as select 123 as [d10]
select 123d10
June 11, 2009 5:57 PM
 

Dave Turpin, Database Professional » Letters and Money in ISNUMERIC may return 1 said:

November 8, 2009 5:31 PM
You need to sign in to comment on this blog

About Johnm

John Magnabosco manages the Data Services Group at one of the fastest growing companies in the United States. He is also the current President and Co-Founder of the Indianapolis Professional Association for SQL Server (IndyPASS), the Speaker Coordinator and Co-Founder of IndyTechFest and the author of the book titled "Protecting SQL Server Data". John is a Microsoft MVP for SQL Server.


















<June 2009>
SuMoTuWeThFrSa
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011
Microsoft Office Communications Server 2007 R2 – Part II
  Once you have set up Office Communication Server 2007 R2 to provide IM within the rganisation, the... Read more...

Mission Critical: Database Design
 There is nothing like a checklist to make sure you've completed all the tasks in designing a database,... Read more...

SQL Server Intellisense VS. Red Gate SQL Prompt
 Fabiano Amorim is hooked on today's Integrated Development Environments with built-in Intellisense, so... Read more...

Doug Crockford: Geek of the Week
  Doug Crockford is the man behind JavaScript Object Notation (JSON). He is a well-known critic of XML... Read more...

Raw Materials: Mirror, Mirror, on the Desk
 Seeing ourselves as we see ourselves. Read more...