|
Execute the following
Microsoft SQL Server T-SQL scripts in Query Analyzer or Management Studio Query Editor to demonstrate the usage of the IsNumeric function.
-- SQL Server isnumeric quick usage syntax - t sql isnumeric
SELECT TOP (3) AddressID, City, PostalCode, ISNUMERIC(PostalCode) AS IsZipNumeric
FROM AdventureWorks2008.Person.Address
ORDER BY NEWID()
/* AddressID City PostalCode IsZipNumeric
11497 Colma 94014 1
11466 Peterborough PB12 0
21744 Olympia 98501 1
*/
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.
The ISNUMERIC function takes a single string expression as parameter and returns:
1 for numeric values
0 for non-numeric values
----------------------------------------------------------------
-- Demonstration of IsNumeric for selected values
----------------------------------------------------------------
USE tempdb;
GO
CREATE TABLE IsNumericDemo (
ID smallint identity primary key,
NumberString varchar(32),
[IsNumeric] bit )
GO
INSERT IsNumericDemo(NumberString) VALUES ('$')
INSERT IsNumericDemo(NumberString) VALUES ('$100')
INSERT IsNumericDemo(NumberString) VALUES ('$200.01')
INSERT IsNumericDemo(NumberString) VALUES ('-$300')
INSERT IsNumericDemo(NumberString) VALUES ('$-400')
INSERT IsNumericDemo(NumberString) VALUES ('$123,568.00')
INSERT IsNumericDemo(NumberString) VALUES ('$234.568.00')
INSERT IsNumericDemo(NumberString) VALUES ('3.E4')
INSERT IsNumericDemo(NumberString) VALUES ('FFFF')
INSERT IsNumericDemo(NumberString) VALUES ('2^10')
INSERT IsNumericDemo(NumberString) VALUES ('0000001')
INSERT IsNumericDemo(NumberString) VALUES ('1234+')
INSERT IsNumericDemo(NumberString) VALUES ('+1234')
INSERT IsNumericDemo(NumberString) VALUES ('twenty')
GO
-- Set the IsNumeric flag
UPDATE IsNumericDemo SET [IsNumeric] = ISNUMERIC(NumberString)
-- Check results
SELECT * FROM IsNumericDemo
GO
/* Results
ID NumberString IsNumeric
1 $ 1
2 $100 1
3 $200.01 1
4 -$300 1
5 $-400 1
6 $123,568.00 1
7 $234.568.00 0
8 3.E4 1
9 FFFF 0
10 2^10 0
11 0000001 1
12 1234+ 0
13 +1234 1
14 twenty 0
*/
-- Cleanup
DROP TABLE IsNumericDemo
GO
------------
-- 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
*/
------------
-- Parsing for numeric data in alphanumeric string
USE Northwind;
SELECT QuantityPerUnit FROM Products ORDER BY ProductName
-- QuantityPerUnit: 10 - 500 g pkgs., 1 kg pkg., 24 - 250 g pkgs.
-- 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
/* Partial results
ProductName QuntityInPackage
Chef Anton's Cajun Seasoning 48
Chef Anton's Gumbo Mix 36
Grandma's Boysenberry Spread 12
Uncle Bob's Organic Dried Pears 12
Northwoods Cranberry Sauce 12
*/
-- 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
/* Partial results
Zip Numeric = 1
94025 1
NULL 0
94618 1
94705 1
*/
-- Filtering non-numeric postal codes
USE AdventureWorks;
SELECT Country=cr.Name,
State=sp.StateProvinceCode,
City,
PostalCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
INNER JOIN Person.CountryRegion cr
ON sp.CountryRegionCode = cr.CountryRegionCode
WHERE ISNUMERIC(PostalCode) = 0
/* Partial results
Country State City PostalCode
Canada ON Ottawa K4B 1S2
Canada BC Burnaby V5A 4X1
Canada BC Haney V2W 1W2
United Kingdom ENG Cambridge CB4 4BZ
*/
-- Filtering for not numeric Size
SELECT * FROM AdventureWorks2008.Production.Product
WHERE Size is not NULL
and ISNUMERIC(Size) = 0 -- NOT NUMERIC
-- 34 rows, Size like S, M, L
/*****
The following query ends in error without ISNUMERIC filtering:
Conversion failed when converting the nvarchar value 'M' to data type int.
****/
SELECT iSize=CONVERT(int, Size), *
FROM AdventureWorks2008.Production.Product
WHERE Size is not NULL
/*****
CONVERT to INT in the following query will not fail due to ISNUMERIC filtering
****/
SELECT iSize=CONVERT(int, Size), *
FROM AdventureWorks2008.Production.Product
WHERE Size is not NULL
and ISNUMERIC(Size) = 1 -- NUMERIC
-- 177 rows, Size like 44, 58, 62
-- Using the CASE function to tag data numeric/alpha
SELECT ProductName=Name, Size,
[SizeType] = CASE
WHEN ISNUMERIC(Size) = 1 THEN 'Numeric'
ELSE 'Alpha/Alphanumeric' END
FROM AdventureWorks2008.Production.Product
WHERE Size is not NULL
ORDER BY SizeType, ProductName
-- 211 rows
/* Partial results
ProductName Size SizeType
Classic Vest, L L Alpha/Alphanumeric
Classic Vest, M M Alpha/Alphanumeric
Classic Vest, S S Alpha/Alphanumeric
*/
-- Using IsNumeric with IF...ELSE
------------------------------------------------------------------
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
------------
-- sql isnumeric function in data cleansing for valid numbers
------------
USE tempdb;
-- sql cast number to varchar - newid - random sort - t-sql top function
-- sql select into table create
-- sql convert number to text - numeric to text - numeric to varchar
SELECT top(3) PurchaseOrderID,
stringSubTotal = CAST (SubTotal AS varchar)
INTO NumberValidation
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
ORDER BY NEWID()
GO
SELECT * FROM NumberValidation
/* Results
PurchaseOrderID stringSubTotal
1027 20397.30
2815 525.00
340 7623.00
*/
-- SQL update with top
UPDATE TOP(1) NumberValidation
SET stringSubTotal = '91117 95'
GO
-- SQL string to numeric conversion fails without validation
SELECT PurchaseOrderID, SubTotal = CAST (stringSubTotal as money)
FROM NumberValidation
GO
/* Msg 235, Level 16, State 0, Line 2
Cannot convert a char value to money. The char value has incorrect syntax.
*/
-- sql isnumeric - filter for valid numbers
SELECT PurchaseOrderID, SubTotal = CAST (stringSubTotal as money)
FROM NumberValidation
WHERE ISnumeric(stringSubTotal) = 1
GO
/* Results
PurchaseOrderID SubTotal
2815 525.00
340 7623.00
*/
-- SQL drop table
DROP TABLE NumberValidation
Go
------------
------------
-- Using ISNUMERIC function in ORDER BY clause
------------
-- SQL isnumeric order by
-- SQL case statement
USE AdventureWorks2008;
SELECT p.BusinessEntityID, FullName=FirstName+' '+LastName,
City, PostalCode
FROM Person.Person p
INNER JOIN Person.BusinessEntityAddress bea
ON bea.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address a
ON bea.AddressID = a.AddressID
ORDER BY CASE
WHEN ISNUMERIC(PostalCode)=0 THEN 1
ELSE 2 END, City, FullName DESC
GO
/* Partial results
BusinessEntityID FullName City PostalCode
15254 Heidi Arun Basingstoke Hants RG24 8PL
5329 Dale Andersen Basingstoke Hants RG24 8PL
17511 Valerie Zhu Berks SL4 1RH
8625 Tasha Lal Berks SL4 1RH
20020 Tara Raji Berks SL4 1RH
5001 Suzanne Li Berks SL4 1RH
*/
------------
-- Using ISNUMERIC and LIKE for numeric/digits testing
------------
-- SQL numeric test
-- SQL isnumeric
-- ISNUMERIC sql
SELECT TOP 5 CompanyName,
City=City+', '+Country,
PostalCode,
IsPostalCodeNumeric =
CASE
WHEN Isnumeric(PostalCode) = 1 THEN 'Numeric'
ELSE 'Not Numeric'
END
FROM Northwind.dbo.Suppliers
ORDER BY Newid()
GO
/* Results
CompanyName City PostalCode IsPostalCodeNumeric
Refrescos Americanas Sao Paulo, Brazil 5442 Numeric
Zaanse Snoepfabriek Zaandam, Netherlands 9999 ZZ Not Numeric
Formaggi Fortini s.r. Ravenna, Italy 48100 Numeric
Gai pâturage Annecy, France 74000 Numeric
Forêts d'érables Ste-Hyacinthe, Canada J2S 7S8 Not Numeric
*/
-- Alternate numeric test with like
-- SQL zipcode test
-- SQL test numeric
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
*/
------------
------------
-- SQL Server stored procedure outputting decimal (numeric) value
------------
-- SQL decimal output parameter
-- SQL power math function
USE AdventureWorks2008;
GO
CREATE PROCEDURE uspDelta @PowerOf2 INT,
@Parm DECIMAL(38,3) OUTPUT
AS
BEGIN
SET @Parm = POWER(CONVERT(BIGINT,2), @PowerOf2)+0.789
RETURN (ISNUMERIC(CAST (@Parm as varchar)))
END
GO
-- Execute stored procedure
-- Outputs: @Isnumeric, @DecimalParm
DECLARE @DecimalParm DECIMAL(38,3), @IsNumeric tinyint;
EXEC @IsNumeric = uspDelta 50, @DecimalParm OUTPUT;
SELECT IsItNumeric = @IsNumeric, sprocResult = @DecimalParm;
GO
/* Result
IsItNumeric sprocResult
1 1125899906842624.789
*/
-- Cleanup
DROP PROC uspDelta
GO
------------
|
