Home Library Learn Downloads Support Community
Sign in | United States - English | |
8 out of 11 rated this helpful - Rate this topic

ISNUMERIC (Transact-SQL)

SQL Server 2008

Determines whether an expression is a valid numeric type.

Topic link iconTransact-SQL Syntax Conventions

Copy 

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

Note 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 Using Monetary Data.

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

Copy 
USE AdventureWorks;
GO
SELECT City, PostalCode
FROM Person.Address 
WHERE ISNUMERIC(PostalCode)<> 1;
GO
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
CR/LF makes a string numeric?
We just found ISNUMERIC() returning 1 when we passed it an nvarchar field containing a nothing but a few line feeds (CRLF) - I can't see how this should ever be considered numeric?? Note, the is in SQL Server 2008 with the latest service pack, but not R2.
History