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 to restrict the result.

SQL Server ISNUMERIC Function Syntax

The syntax of the 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. The 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 Server 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 the 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 the below statement, We used the IsNumeric function to check whether the LastName column is Numeric or not. We also reassigned the new one called Name_Flag using the ALIAS Column in the 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;
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, the 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('
ISNUMERIC Function 5

SQL ISNUMERIC in Case Statement

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

ISNUMERIC CODE

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

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