SQL ISNUMERIC Function

The SQL ISNUMERIC function validates whether an expression is Numeric or not. And if the value is Numeric, then the ISNUMERIC function will return one; otherwise, it will return 0.

For example, as an e-commerce owner, you want to send Christmas gift cards to all your customers in the USA. What if your result returns strange postcodes (non-numeric Postcodes, something like ST1 RG4). To get rid of these situations, we can use ISNUMERIC in SQL Server to restrict the result.

SQL ISNUMERIC Function Syntax

The syntax of the SQL Server ISNUMERIC function is

SELECT ISNUMERIC ([Check_Expression])
FROM [Source]
  • Check_Expression: Please specify the valid expression or column name on which you want to check for Numeric values. IsNumeric function will check whether the column value is numeric or not.

The ISNUMERIC function will return 1 when the user-specified expression is a valid numeric data type. Otherwise, it will return 0. The following table will show you the correct numeric data types

inttinyint
smallintbigint
moneysmallmoney
floatdecimal
numericfloat

The SQL Server ISNUMERIC function returns 1 for some characters that are not numbers, such as minus (-), plus (+), Comma (,), and valid currency symbols like US Dollar ($). For this ISNUMERIC function example, We are going to use the below-shown data

and the table structure is

SQL ISNUMERIC function

If you observe the above screenshot, there are five columns, and 2 of them are NVarchar type, 1 is Money type, another one is Float, and the last one is Integer.

In this example, we are going to check whether the Lastname, [YearlyIncome], [Tax], and [Variable pay] columns are holding numeric data or not using SQL Server IsNumeric function

SELECT [FirstName]
      ,[LastName]
      ,ISNUMERIC([LastName]) AS [Name_Flag]
      ,[YearlyIncome]	
      ,ISNUMERIC([YearlyIncome]) AS [Income_Flag]
      ,[Tax]
      ,ISNUMERIC([Tax]) AS [Tax_Flag]
      ,[Variable Pay]
      ,ISNUMERIC([Variable Pay]) AS [Pay_Flag]
  FROM [SQLIsNumeric]
SQL ISNUMERIC Function 3

In below statement, We used IsNumeric function to check whether the LastName column is Numeric or not. We also reassigned the new one called Name_Flag using ALIAS Column in SQL Server.

ISNUMERIC([LastName]) AS [Name_Flag]

SQL ISNUMERIC Function Example 1

The following query will show multiple ways to use the IsNumeric function.

SELECT ISNUMERIC('12345') AS Example1;
SELECT ISNUMERIC(12345) AS Example2;
SELECT ISNUMERIC(2 * 4 + 5) AS Example3;
SELECT ISNUMERIC('2.0e9') AS Example4;
SELECT ISNUMERIC($12345) AS Example5;

SELECT ISNUMERIC('A12345') AS Example6;
SELECT ISNUMERIC('09-08-2016') AS Example7;
SQL ISNUMERIC Function 4

Below lines of code will convert the string ‘12345’ to numeric and check whether numeric or not.

SELECT ISNUMERIC('12345') AS Example1;

As we said before, sql server IsNumeric function will return 1 for the US dollar ($)

SELECT ISNUMERIC($12345) AS Example5;

In the below statement, We used both Alphabet and numeric. It returns zero because it is unable to convert A

SELECT ISNUMERIC('A12345') AS Example6;

ISNUMERIC Function Example 2

The following query will show you the typical IsNumeric functionality.

SELECT ISNUMERIC('-') AS Example1;
SELECT ISNUMERIC('+') AS Example2;
SELECT ISNUMERIC('
SQL ISNUMERIC Function 5

SQL ISNUMERIC in Case Statement

In this example, we are going to use the Sql Server IsNumeric function inside the Case statement. Let us see the data inside the sql table before we get into the example.

SQL ISNUMERIC Function 6

ISNUMERIC CODE

SELECT [Name]
      ,[City]
      ,[PostCode]
      ,CASE 
	   WHEN ISNUMERIC([PostCode]) <> 1 THEN 'N'
	   ELSE 'Y'
       END AS [PostCode_Flag]
  FROM [IsNumeric Example2]
SQL ISNUMERIC Function 7

The following statement will check whether the result of ISNUMERIC([PostCode]) is not equal to 1 or not. If the result is true, then it will return ‘N’, otherwise return ‘Y’.

WHEN ISNUMERIC([PostCode]) <> 1 THEN 'N'  ELSE 'Y'
Categories SQL