Edit

ISNUMERIC (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Determines whether an expression is a valid numeric type.

Transact-SQL syntax conventions

Syntax

ISNUMERIC ( expression )

Note

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

expression
Is the expression to be evaluated.

Return Types

int

Remarks

ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid numeric data types include the following:

Area Numeric data types
Exact Numerics bigint, int, smallint, tinyint, bit
Fixed Precision decimal, numeric
Approximate float, real
Monetary Values money, smallmoney

Note

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see money and smallmoney (Transact-SQL).

Examples

The following example uses ISNUMERIC to return all the postal codes that are not numeric values.

USE AdventureWorks2022;  
GO  
SELECT City, PostalCode  
FROM Person.Address   
WHERE ISNUMERIC(PostalCode) <> 1;  
GO

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

The following example uses ISNUMERIC to return all the postal codes that are not numeric values.

USE master;  
GO  
SELECT name, ISNUMERIC(name) AS IsNameANumber, database_id, ISNUMERIC(database_id) AS IsIdANumber   
FROM sys.databases;  
GO

See also