TechNet
Export (0) Print
Expand All

ISNUMERIC (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Determines whether an expression is a valid numeric type.

Topic link icon Transact-SQL Syntax Conventions


ISNUMERIC ( expression )
-- Azure SQL Data Warehouse and Parallel Data Warehouse
ISNUMERIC (expression )

expression

Is the expression to be evaluated.

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:

int

numeric

bigint

money

smallint

smallmoney

tinyint

float

decimal

real

System_CAPS_noteNote

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).

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

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

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

Community Additions

ADD
Show:
© 2016 Microsoft