ISNUMERIC (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics 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 (12.x) and earlier versions, 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
Feedback
Submit and view feedback for