Hadi Fadlallah
Listing non-numeric characters acceptable using the SQL Server ISNUMERIC function while in the middle of a numeric string

An overview of the SQL Server ISNUMERIC function

July 2, 2021 by

This article gives an overview of the SQL Server ISNUMERIC built-in system function. It explains how to use this function, illustrates its limitations, and provides some alternatives.

What is the SQL Server ISNUMERIC function?

As defined in the official Microsoft SQL Server documentation, the ISNUMERIC function determines whether an expression is a valid numeric type. It is a scalar function that takes a string expression as a parameter and returns an integer. If the input expression is evaluated to a valid numeric data type, SQL Server ISNUMERIC returns 1; otherwise, it returns 0. A valid numeric data type is one of the following types: bit, tinyint, smallint, int, bigint, decimal, numeric, float, real, money, smallmoney.

Characters considered as valid numeric data type

Since not only exact numbers are considered valid numeric data types, the ISNUMERIC function will return 1 even if the expression contains some symbols and alphabets.

In the official documentation, it is mentioned that “ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).”

To list all acceptable characters, we used the following SQL Command:

This SQL Command generates numbers ranging from 0 to 255 (ASCII characters codes) and uses the CHAR() function to retrieve the corresponding character for each ASCII code. Then we use the SQL Server ISNUMERIC function to check whether this value is considered as a valid numeric data type or not. The following table contains the SQL Command result:

ASCII Code

Character

Is Numeric

9

True

10

True

11

True

12

True

13

True

36

$

True

43

+

True

44

,

True

45

True

46

.

True

92

\

True

128

True

160

 

True

162

¢

True

163

£

True

164

¤

True

165

¥

True

Table 1 – Characters considered as a valid numeric data type

List non-numeric characters acceptable using the SQL Server ISNUMERIC function

Figure 1 – Characters considered as a valid numeric data type

The result table shows that the SQL Server ISNUMERIC function accepts currency symbols and signs (plus and minus). Since SQL Server didn’t show all characters (ASCII CODE: 9,10,11,12,13,160), we referred to the ASCII code table to find them:

ASCII CODE

Description

9

Horizontal tab (HT)

10

Line feed (LF)

11

Vertical tab (VT)

12

New page/form feed (FF)

13

Carriage return (CR)

160

No-break space

Table 2 – ASCII codes description

As shown in the table above, the SQL Server ISNUMERIC function ignores all kinds of whitespaces when evaluating the input expression.

Other acceptable characters

In the previous section, we listed the single characters that are considered valid numeric types. Moreover, there are other acceptable characters if used in the middle of a numeric value, such as the “e” character used in the scientific notation, as an example 12e2252. To list those characters, we modified the previous SQL Command to put all characters within a numeric string and evaluate the whole expression using the ISNUMERIC function.

The following table contains the SQL Command result:

ASCII code

Character

Expression

Is Numeric

44

,

12,45

True

46

.

12.45

True

68

D

12D45

True

69

E

12E45

True

100

d

12d45

True

101

e

12e45

True

Table 3 – SQL Command result

Listing non-numeric characters acceptable using the SQL Server ISNUMERIC function while in the middle of a numeric string

Figure 2 – SQL Command to list characters acceptable in the middle of a numeric expression

As shown in the table above, the dot and comma characters are supported since they are used in decimal and monetary values. Besides, as mentioned above, the “E” and “e” characters are used in scientific notations. I searched for official documentation mentioning the “D” and “d” characters with no success. I found a Stack Overflow answer mentioning the following note from the VB6 documentation, which explains why: “Floating-point values can be expressed as mmmEeee or mmmDeee, in which mmm is the mantissa and eee is the exponent (a power of 10). The highest positive value of a Single data type is 3.402823E+38, or 3.4 times 10 to the 38th power; the highest positive value of a Double data type is 1.79769313486232D+308, or about 1.8 times 10 to the 308th power. Using D to separate the mantissa and exponent in a numeric literal causes the value to be treated as a Double data type. Likewise, using E in the same fashion treats the value as a Single data type.”

Limitations

There are two limitations for the SQL Server ISNUMERIC function:

  1. It is a scalar function, which means it doesn’t use indexes which may cause slow performance
  2. It cannot validate numeric expressions with no symbols and alphabets, which is frequently required during the data preparation phase

Alternatives

There are some alternatives for the ISNUMERIC function to solve the second limitation we mentioned previously:

Using the CASE statement with the LIKE operator

You can simply use a case statement to check if an expression/column contains any characters other than numbers as follows:

The image below shows how this command will return 0 for a numeric expression that contains the character “D” while ISNUMERIC() returns 1.

Comparing the CASE STatment with the SQL Server ISNUMERIC function

Figure 3 – Using a CASE statement to check if an expression contains any character other than numbers

Using TRY_CONVERT, TRY_PARSE, and TRY_CAST functions

Another alternative is to use the TRY_CONVERT, TRY_CAST, or TRY_PARSE functions to check if an expression can be converted to a specific data type. Those functions will return the converted value if it can be evaluated as the specified data type; otherwise, they will return NULL. As an example:

The following image shows the difference between using the TRY_CAST function and the ISNUMERIC function:

Comparing the TRY_CAST function with the SQL Server ISNUMERIC function

Figure 4 – Difference between TRY_CAST() and ISNUMERIC() functions

Summary

In this article, we explained the SQL Server ISNUMERIC built-in scalar function briefly. Then, we illustrated the acceptable non-numeric characters using this function. Finally, we mentioned some of the limitations and alternatives.

Hadi Fadlallah
SQL commands, T-SQL

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a Neo4j and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah

456 Views