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:

--Dry Run ISNUMERIC Function
SELECT ISNUMERIC(10) AS ISNUMERIC_10_Result

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

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

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

[expand title =”Code“]

-- Create sample database (SQLDevBlogV3)
CREATE DATABASE SQLDevBlogV3;
GO


USE SQLDevBlogV3;

-- (1) Create the Author table in the sample database
CREATE TABLE Author (
  AuthorId INT PRIMARY KEY IDENTITY (1, 1)
 ,Name VARCHAR(40)
 ,RegistrationDate DATETIME2
 ,TotalArticles INT
 ,Notes VARCHAR(400)
)

-- (2) Create the Category table in the sample database
CREATE TABLE Category (
  CategoryId INT PRIMARY KEY IDENTITY (1, 1)
 ,Name VARCHAR(50)
 ,Notes VARCHAR(400)
)

-- (3) Create the Article table in the sample database
CREATE TABLE Article (
  ArticleId INT PRIMARY KEY IDENTITY (1, 1)
 ,CategoryId INT
 ,AuthorId INT
 ,Title VARCHAR(150)
 ,Published DATETIME2
 ,Notes VARCHAR(400)  
)

-- Adding foreign keys for the Author and Article categories
ALTER TABLE Article ADD CONSTRAINT FK_Category_CategoryId FOREIGN KEY (CategoryId) REFERENCES Category (CategoryId)
ALTER TABLE Article ADD CONSTRAINT FK_Author_AuthorId FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId)

GO


-- (5) Populating the Author table
INSERT INTO Author (Name, RegistrationDate, TotalArticles,Notes)
  VALUES ('Sam', '2017-01-01', 20,'Database Analyst'),
  ('Asif', '2017-01-02',15, 'Database and Business Intelligence Developer'),
  ('Sadaf', '2018-01-01', 30,'Database Analyst Programmer')

-- (6) Populating the Category table
INSERT INTO Category (Name, Notes)
  VALUES ('Development', 'Articles about database development'),
  ('Testing', 'Database testing related articles'),
  ('DLM', 'Database lifecycle management')

-- (7) Populating the Article table
INSERT INTO Article (CategoryId, AuthorId, Title, Published, Notes)
  VALUES (1, 1, 'Fundamentals of SQL Database Development', '02-01-2018', ''),
  (1, 2, 'Advanced Database Development', '02-01-2018', ''),
  (2, 3, 'All About Database Testing', '03-01-2018', '');
GO

[/expand]

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

-- View all authors
SELECT [AuthorId]
      ,[Name]
      ,[RegistrationDate]
      ,[TotalArticles]
      ,[Notes]	
  FROM [dbo].[Author]

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:

-- Creating a stored procedure to add new authors to the Author table
CREATE PROCEDURE AddAuthor
@Name VARCHAR(40),
@RegistrationDate DATETIME2,
@TotalArticles INT,
@Notes VARCHAR(400)
AS
SET NOCOUNT ON
INSERT INTO dbo.Author
(
  Name
 ,RegistrationDate
 ,TotalArticles
 ,Notes
)
VALUES
(
  @Name 
 ,@RegistrationDate 
 ,@TotalArticles 
 ,@Notes 
);
GO

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:

-- Adding a new author by using the AddAuthor stored procedure
EXEC dbo.AddAuthor "Adil","2019-01-03",18,"SQL Developer"

View all authors to see the newly-added one:

-- View all authors
SELECT [AuthorId]
      ,[Name]
      ,[RegistrationDate]
      ,[TotalArticles]
      ,[Notes]
  FROM [dbo].[Author]

The output is shown below:

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:

-- Adding a new author while expressing TotalArticles in words rather than as a number
EXEC dbo.AddAuthor "Peter","2019-01-03",'Three','Business Intelligence Developer'

Running the script results in the following 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:

Creating and Populating the AuthorAge Table

This inconsistent table is imported into the sample database:

--Creating the AuthorAge table
CREATE TABLE AuthorAge (
  AuthorId INT 
 ,Age VARCHAR(50) NOT NULL
 ,CONSTRAINT PK_AuthorAge_AuthorId PRIMARY KEY CLUSTERED (AuthorId)
) ON [PRIMARY]
GO

-- Populating AuthorAge with data from our Excel file
INSERT INTO [dbo].[AuthorAge]
           ([AuthorId]
           ,[Age])
     VALUES
           (1,'32'),
		   (2,'Twenty Nine'),
		   (3,'27'),
		   (4,'34');

GO

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

--View the AuthorAge table
SELECT AuthorId,Age FROM dbo.AuthorAge

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:

-- Adding the Age column to the Author table
ALTER TABLE dbo.Author
  ADD Age INT NULL;
GO

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:

--View authors with age expressed in numbers using ISNUMERIC
SELECT A.AuthorId,A.Name,A.RegistrationDate, AA.Age,A.TotalArticles
FROM Author A INNER JOIN AuthorAge AA
ON A.AuthorId=AA.AuthorId
WHERE ISNUMERIC(AA.Age)=1

The results are as follows:

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:

--Merging the AuthorAge and Author tables; only records with numeric age values are included
UPDATE Author
SET Age=AA.Age
FROM Author A INNER JOIN AuthorAge AA
ON A.AuthorId=AA.AuthorId
WHERE ISNUMERIC(AA.Age)=1

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:

-- View all authors with numeric age values
SELECT A.AuthorId,A.Name,A.RegistrationDate, A.Age,A.TotalArticles FROM Author A

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.