ISNUMERIC() FUNCTION IN SQL SERVER WITH EXAMLPES

• The ISNUMERIC function can be used for safe coding by checking the string data prior to CONVERT/CAST to numeric. The ISNUMERIC function as filter is important in data cleansing when internal/external feeds are loaded into the database or data warehouse. 

SYNTAX:

                 ISNUMERIC( expression )

Expression:
           expression is the value to test whether it is a numeric value.

Note:

• The ISNUMERIC function returns 1, if the expression is a valid number.
• The ISNUMERIC function returns 0, if the expression is NOT a valid number.

numeric datatypes:

  int,numeric,bigint,money,smallint,smallmoney,tinyint,float,decimal,real

SELECT ISNUMERIC('')------0.  This is understandable, but your logicmay                                              want    to default these to zero.
SELECT ISNUMERIC(' ')--0.  This is understandable, but your logic may want to default these to zero.

SELECT ISNUMERIC('%')--0.

SELECT ISNUMERIC('1%')--0.

SELECT ISNUMERIC('e')--0.
SELECT ISNUMERIC('  ')--1.  --Tab.
SELECT ISNUMERIC(CHAR(0x09))--1.  --Tab.
SELECT ISNUMERIC(',')--1.
SELECT ISNUMERIC('.')--1.
SELECT ISNUMERIC('-')--1.
SELECT ISNUMERIC('+')--1.
SELECT ISNUMERIC('$')--1.
SELECT ISNUMERIC('\')--1.  '
SELECT ISNUMERIC('e0')--1.
SELECT ISNUMERIC('100e-999')--1.  No SQL-Server datatype could hold this number, though it is real.
SELECT ISNUMERIC('3000000000')--1.  This is bigger than what an Int could hold, so code for these too.
SELECT ISNUMERIC('1234567890123456789012345678901234567890')--1.  Note: This is larger than what the biggest Decimal(38) can hold.
SELECT ISNUMERIC('- 1')--1.
SELECT ISNUMERIC('  1  ')--1.
SELECT ISNUMERIC('True')--0.
SELECT ISNUMERIC('1/2')--0.  No love for fractions.

EXAMPLES:

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

-- SQL Server ISNUMERIC & CASE quick usage examples - t sql isnumeric and case functions

-- T-SQL IsNumber, IsINT, IsMoney - filtering numeric data from text columns
SELECT ISNUMERIC('$12.09'), ISNUMERIC('12.09'), ISNUMERIC('$'),ISNUMERIC('Alpha')

--          1                 1                 1                             0

SELECT ISNUMERIC('-12.09'), ISNUMERIC('1209')ISNUMERIC('1.0e9'),ISNUMERIC('A001')

--          1                 1                 1                             0

---isnumeric() function with case 

SELECT   TOP (4) AddressID,
                   City,
                   PostalCode,
                   CASE
                     WHEN ISNUMERIC(PostalCode) = 1 THEN 'Y'
                     ELSE 'N'
                   END AS IsZipNumeric
FROM     AdventureWorks2008.Person.Address
ORDER BY NEWID()
/* AddressID   City           PostalCode  IsZipNumeric
27625       Santa Monica      90401       Y
23787       London            SE1 8HL     N
24776       El Cajon          92020       Y
22120       Wollongong        2500        Y    */

------------

-- SQL ISALPHANUMERIC check

------------

-- SQL not alphanumeric string test - sql patindex pattern matching

SELECT DISTINCT LastName

FROM   AdventureWorks.Person.Contact

WHERE  PATINDEX('%[^A-Za-z0-9]%',LastName) > 0

GO

/* Partial results

LastName

Mensa-Annan

Van Eaton

De Oliveira

*/

-- SQL ALPHANUMERIC test - isAlphaNumeric

SELECT DISTINCT LastName

FROM   AdventureWorks.Person.Contact

WHERE  PATINDEX('%[^A-Za-z0-9]%',LastName)= 0

GO

/* Partial results

LastName

Abbas

Abel

Abercrombie

*/

-- When left 3 characters satisfy isnumeric test, we convert to int

SELECT

  ProductName,

  QuntityInPackage=convert(int,left(QuantityPerUnit,3))

FROM Products

WHERE ISNUMERIC(left(QuantityPerUnit,3))=1

ORDER BY ProductName

-- Canadian & UK zipcodes would not be numeric - NULL also not numeric (function yields 0)

USE pubs;

SELECT

  Zip=zip,

  [Numeric = 1] = ISNUMERIC(zip)

FROM authors

------------

-- Using IsNumeric with IF...ELSE conditional construct

------------

DECLARE @StringNumber varchar(32)

SET @StringNumber = '12,000,000'

IF EXISTS( SELECT * WHERE ISNUMERIC(@StringNumber) = 1)

      PRINT 'VALID NUMBER: ' + @StringNumber

ELSE

    PRINT 'INVALID NUMBER: ' + @StringNumber

GO

-- Result: VALID NUMBER: 12,000,000

DECLARE @StringNumber varchar(32)

SET @StringNumber = '12-34'

IF EXISTS( SELECT * WHERE ISNUMERIC(@StringNumber) = 1)

      PRINT 'VALID NUMBER: ' + @StringNumber

ELSE

    PRINT 'INVALID NUMBER: ' + @StringNumber

GO

-- Result: INVALID NUMBER: 12-34

- Alternate numeric test with like

-- SQL zipcode test - SQL test numeric - SQL CASE function

SELECT   TOP 5 CompanyName,

               City=City+', '+Country,

               PostalCode,

               [IsNumeric] =

               CASE

                   WHEN PostalCode like '[0-9][0-9][0-9][0-9][0-9]'

                     THEN '5-Digit Numeric'

                   ELSE 'Not 5-Digit Numeric'

               END

FROM     Northwind.dbo.Suppliers

ORDER BY Newid()

GO

/* Results

CompanyName             City                    PostalCode        IsNumeric

Escargots Nouveaux      Montceau, France        71300 5-Digit     Numeric

Norske Meierier         Sandvika, Norway        1320  Not 5-Digit Numeric

Pavlova, Ltd.           Melbourne, Australia    3058  Not 5-Digit Numeric

Zaanse Snoepfabriek     Zaandam, Netherlands    9999 ZZ           Not 5-Digit Numeric

Exotic Liquids          London, UK              EC1 4SD           Not 5-Digit Numeric

*/