How to Properly Use the T-SQL IsNumeric Function

This article is focused on the T-SQL (Transact-SQL) IsNumeric function and its proper use in day-to-day SQL scripting tasks.

We will also see why it is important to understand how and why IsNumeric can be used – both incorrectly and correctly.

There may be some better alternatives to IsNumeric depending on the context. However, in the cases we’re going to cover in this article, I see this function as the best possible choice.

About IsNumeric

First of all, let’s get familiar with IsNumeric. Here’s what we can say about it after reading the related information in the Microsoft documentation:

Microsoft Definition

The IsNumeric function determines whether an expression can be evaluated as a number. Here, the expression can consist of symbols and operators which are evaluated as a single data value by SQL Server Database Engine.

Please refer to my article Basic to Complex Uses of Not Equal in T-SQL to get more information about expressions and operators.

Simple Definition

The IsNumeric function tells us if something we passed to it (a variable, constant or a column) is a number or not.

How Does IsNumeric Work

It is quite obvious what the function does based on the definition. However, it may not be clear how to get the result of this function.

In reality, it’s also rather simple – IsNumeric returns 1 if the expression we passed to it is numeric or 0 if it’s not.

Dry Run Example

One of the simplest examples we can come up with is passing the number 10 to the function to check how it works:

When we run the script, the following output is generated:

Dry Run

Since IsNumeric returns ‘1’ when 10 is passed to it as an expression, we can be sure that 10 is a numeric value. Of course, we already know it. But, in this way, we can get a basic understanding of how this function works.

Compatibility

According to Microsoft documentation, the IsNumeric function is compatible with the following SQL database versions:

  1. SQL Server 2008 and upward
  2. Azure SQL Database
  3. Azure SQL Data Warehouse
  4. Parallel Data Warehouse

Syntax

ISNUMERIC expression (constant, variable, column etc.)

Pre-requisites

This article is mostly written for beginners, but there are still some pre-requisites which must be kept in mind if you want to understand it better.

Familiarity with T-SQL

This article assumes that the readers have a basic T-SQL know-how and are capable of writing and running basic SQL scripts.

Setup Sample Database (SQLDevBlogV3)

This article requires the SQLDevBlogV3 sample database to be set. This database consists of three tables:

  1. Article
  2. Author
  3. Category

SQLDevBlogV3 Sample Database

You can setup the SQLDevBlogV3 database by running the following code:

Code

When this is done, you can view all authors by running the following script:

Viewing All Author Records

Improper Use of the IsNumeric function

In this section, we are going to explore the improper use of IsNumeric.

Please remember that the upcoming examples should be ran against the SQLDevBlogV3 database we created earlier.

In the sample database, we have the Author table which contains the list of all registered authors.

Adding Authors

Let’s suppose that a business requirement to add new authors was sent to the development team.

Creating the AddAuthor Stored procedure

Attempting to complete the business requirement, we will create a stored procedure which is capable of adding new authors to the Author table.

The AddAuthor stored procedure can be created as follows:

Adding a New Author by Using the AddAuthor Procedure

Now. let’s add a new author named Adil by using the stored procedure we have just written:

View all authors to see the newly-added one:

The output is shown below:

Adding A New Author

As you can see, the newly-added author is now dsiplayed in the complete list of authors.

Adding A New Author by Supplying TotalArticles in Words

Now, let’s add another author – but this time we are purposely inserting a Total Articles value expressed in words rather than as a number:

Running the script results in the following error:

Non-Numeric Value Error

This error was caused because we passed a string value (‘Three’) as an argument to the stored procedure. A value of numeric type (INT) is expected for Total Articles since we know that the TotalArticle column in our table has the numeric type.

It might be possible to use the IsNumeric function to avoid this conversion error.

Should We Use IsNumeric to Check the TotalArticles Value?

One might think, this means we have to modify our AddAuthor stored procedure. Specifically, making it check if the value supplied for TotalArticles is numeric or not and, if it is, process the request to be added to the Author table.

However, the IsNumeric function will not really help in this scenario due to the following:

  1. The argument of incorrect data type will cause the AddAuthor stored procedure to instantly throw an error, so trying to check the supplied argument within the stored procedure is useless
  2. The IsNumeric function is not really meant to check data types of arguments passed to stored procedures – rather, it has a different objective

We can handle this scenario by using the TRY…CATCH construct which is capable of catching the error before it is thrown by the system (further explanation is beyond the scope of this article).

Proper Use of the IsNumeric function

Now we know that IsNumeric should not be used to validate the data type of parameters passed to stored procedures.

The proper use of IsNumeric function would be to make it check if an expression (e.g., a variable) is numeric or not.

Now, we will implement IsNumeric in a proper and suitable scenario.

Importing Inconsistent Age Column Values

Let’s suppose that this time around we are importing less consistent Age data about authors from an Excel sheet.

The Age column has mixed value types:

Table 1

Creating and Populating the AuthorAge Table

This inconsistent table is imported into the sample database:

Now, let’s view the contents of this table:

Table With Inconsistent Value Types

Adding the Age column to the Author table

Since we are planning to merge the AuthorAge table with the Author table, we will need to add the Age column to the Author table by running the following script:

Viewing authors with numeric Age values by using ISNUMERIC

We require the IsNumeric function to only choose those authors whose age is expressed in numbers. This can be achieved by the following script:

The results are as follows:

Authors With Numeric Age Values

Using ISNUMERIC to Merge AuthorAge with Author table

We are going to merge the AuthorAge table with the Author table, but some work should be done in order to get only numeric age values from AuthorAge and join it with the Age column in the Author table based on Author Id.

IsNumeric comes into action as soon as you run the following SQL script against the sample database:

Now, let’s view all authors whose age is expressed in numbers and who were successfully copied from another table by executing the following code:

Authors With Numeric Age Values 2

With this, you now know how to use the IsNumeric function to both manage tables with inconsistent data types and join them with another tables by filtering out the inconsistent data.

Things to do

Now that you are familiar with the proper use of IsNumeric, it is worth trying the following things to improve your knowledge further:

  1. Add four more records to the original Author table without the age value and then try to join two tables AuthorAge and AuthorAge2 by filtering out the records with age expressed in words and only allowing age expressed in numbers.
  2. Create a table named Data_For_Review and put all records with numeric Age into this table to be reviewed by using IsNumeric.
  3. Create a stored procedure that will use the IsNumeric function to filter out inconsistent data and put this data into the Data_For_Review table.

Haroon Ashraf

Haroon's deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.

He began his professional life as a computer programmer more than 10 years ago working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.

His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).
Haroon Ashraf

Leave a Reply

Your email address will not be published. Required fields are marked *