Oh... be careful, now... IsNumeric cannot be equated to IsAllDigits. IsNumeric will allow currency symbols, $, comma, decimal point, spaces, tabs, and a host of other individual characters. I will also allow for combinations of charcters...

SELECT ISNUMERIC('2d2'), ISNUMERIC('2e2')

In fact, I wrote a pretty hefty article about this on another web site...

ISNUMERIC is not “ALL DIGITS”Submitted by Jeff Moden, 03 Jun 2006

All rights reserved.

Introduction:

There are many cases where you need to ensure that the string data you are working with includes only numeric digits. Most Developers will use the built in ISNUMERIC function to make such a check. Here’s why that’s a bad idea and what to do about it.

What is ISNUMERIC?

“Books OnLine” summarizes the description of the ISNUMERIC function as:

“Determines whether an expression is a valid numeric type.”

and that’s a 100% accurate description that leaves much to be desired. Just what is a “valid numeric type”? Reading further in BOL (short for “Books OnLine), we find additional information:

“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 guarantees that expression can be converted to one of these numeric types.”

Again, read the wording… “when the input expression evaluates to a valid integer”, etc, etc. And, that’s the catch. There are many different things that you may not expect that will evaluate to one of the data types listed in the description of ISNUMERIC and a lot of them are NOT the digits 0-9. ISNUMERIC will return a “1” for all of them.

Let’s consider the most obvious… what will ISNUMERIC(‘-10’) return? What will ISNUMERIC(‘1,000’) return? And how about the not-so-obvious… what will ISNUMERIC('0d1234') or ISNUMERIC('13e20') return? There are many different combinations of letters, numbers, and symbols that can actually be converted to numeric data types and ISNUMERIC will return a “1” for all of them. It’s not a flaw… that’s the way it’s supposed to work!

What IS Actually Considered “Numeric” by ISNUMERIC?

This code will show all of the single characters that ISNUMERIC thinks of as “Numeric”…

--===== Return all characters that ISNUMERIC thinks is numeric

-- (uses values 0-255 from the undocumented spt_Values table

-- instead of a loop from 0-255)

SELECT [Ascii Code] = STR(Number),

[Ascii Character] = CHAR(Number),

[ISNUMERIC Returns] = ISNUMERIC(CHAR(Number))

FROM Master.dbo.spt_Values

WHERE Name IS NULL

AND ISNUMERIC(CHAR(Number)) = 1

That code produces the following list of characters…

Ascii Code Ascii Character ISNUMERIC Returns

---------- --------------- -----------------

9 1

10

1

11

1

12 1

13

1

36 $ 1

43 + 1

44 , 1

45 - 1

46 . 1

48 0 1

49 1 1

50 2 1

51 3 1

52 4 1

53 5 1

54 6 1

55 7 1

56 8 1

57 9 1

128 € 1

160 1

163 £ 1

164 ¤ 1

165 ¥ 1

What are these characters?

Ascii 9 is a TAB character and is included because a column of numbers is frequently delimited by a TAB.

Ascii 10 is a Line Feed character and is included because the last column of numbers is frequently terminated by a Line Feed character.

Ascii 11 is a Vertical Tab character and is included because the last column of numbers is frequently terminated by a Vertical Tab character.

Ascii 12 is a Form Feed character and is included because the last column numbers of the last row is sometimes terminated by a Form Feed character.

Ascii 13 is a Carriage Return character and is included because the last column of numbers is frequently terminated by a Carriage Return character.

Ascii 36 (Dollar sign), 128 (Euro sign), 163 (British Pound sign), and 164 (Yen sign) are included because they are frequently used as enumerators to identify the type of number or, in this case, the currency type the number is meant to represent.

Ascii 43 (Plus sign), 44 (Comma), 45 (Minus sign), and 46 (Decimal place) are included because they are frequently included in numeric columns to mark where on the number line the number appears and for simple formatting.

Ascii 160 is a special "hard space" and is included because it is frequently used to left pad numeric columns so the column of numbers appears to be right justified.

Ascii 32 is a "soft space" and is not included because a single space does not usually represent a column of numbers. Ascii 32 is, however, a valid numeric character when used to create right justified numbers as is Ascii 160 but a single Ascii 32 character is NOT numeric. In fact, a string of Ascii 32 spaces is not considered to be numeric but a string of spaces with even a single digit in it is considered to be numeric.

Ascii 164 is a special character and is included because it is frequently used by accountants and some software to indicate a total or subtotal of some type. It is also used by some to indicate they don't know what the enumerator is.

Ascii 48-59 are included because they represent the digits 0 through 9

Set of Characters Treated as “Numeric” by ISNUMERIC

Do notice that "e" and "d" (everybody forgets about this) are not included as numeric in the results because a single "e" or "d is NOT considered to be numeric. HOWEVER, these letters are for two different forms of scientific notation. So, if you have anything that looks like the following, ISNUMERIC will identify them as “Numeric”…

SELECT ISNUMERIC('0d2345')

SELECT ISNUMERIC('12e34')

The “Rational” Solution

Hopefully, I’ve proven that ISNUMERIC is NOT the way to determine if a value or a column of values IS ALL DIGITS. So, what to do? We could write something really complex that loops through each character to see if it’s a digit… or … we can use a very simple rational expression to do the dirty work for us. The formula is…

NOT LIKE '%[^0-9]%'

… and it can be used directly (preferred method for performance reasons)…

SELECT *

FROM sometable

WHERE somecolumn NOT LIKE '%[^0-9]%'

… or, if you don’t mind the performance hit, you can create your own “IsAllDigits” function…

CREATE FUNCTION dbo.IsAllDigits

/********************************************************************

Purpose:

This function will return a 1 if the string parameter contains only

numeric digits and will return a 0 in all other cases.

--Jeff Moden

********************************************************************/

--===== Declare the I/O parameters

(@MyString VARCHAR(8000))

RETURNS INT

AS

BEGIN

RETURN (SELECT CASE

WHEN @MyString NOT LIKE '%[^0-9]%'

THEN 1

ELSE 0

END)

END

If you have any questions on this article, please don’t hesitate to post them. And thanks for taking the time to read it and, perhaps, get your favorable vote ?.