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:

Table 1 – Characters considered as a valid numeric data type

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:

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:

Table 3 – SQL Command result

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.

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:

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.

• Author
• Recent Posts

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 MongoDB, 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

Latest posts by Hadi Fadlallah (see all)

• An overview of SQL Server monitoring tools - December 12, 2023
• Different methods for monitoring MongoDB databases - June 14, 2023
• Learn SQL: Insert multiple rows commands - March 6, 2023