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
*/
