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.