ISNUMERIC (Transact-SQL)
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