SQL ISNUMERIC Function

The SQL Server 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 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. It will check whether the column value is numeric or not.

The SQL 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 example, we are going to use the below-shown data.

Table 1

and the table structure is.

Table Structure 2

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.

For the Last Name, we used it to check whether the LastName column is Numeric or not. We also reassigned the new one called Name_Flag using the ALIAS Column in SQL Server.

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

SQL ISNUMERIC Function Example

The following query will show multiple ways to use this function. The first line of code will convert the string ‘12345’ to numeric and check whether numeric or not.

As we said before, it will return 1 for the US dollar ($), and we used it in the fifth statement.

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

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;
Example 4

Example 2

The following query will show you the typical functionality.

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

SQL ISNUMERIC in Case Statement

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

SQL ISNUMERIC Function in CASE 6

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

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