Edit

ISNUMERIC (Transact-SQL)

  • Article

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

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 items:

Area Numeric data types
Exact numerics bigint, int, smallint, tinyint, bit
Fixed precision decimal, numeric
Approximate float, real
Monetary values money, smallmoney

ISNUMERIC returns 1 for some characters that aren't 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 aren't 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 whether the database name and ID are numeric values.

USE master;
GO

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

Related content