Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the usage of the ISNUMERIC function and CASE expression.
-- T-SQL ISNUMERIC function usage - QUICK SYNTAX - 0 means not numeric
SELECT AlphaNumericZip=COUNT(*) FROM AdventureWorks.Person.[Address]
WHERE ISNUMERIC (PostalCode) = 0 -- 3644
-- 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')
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
------------
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 */
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 */
------------
-- CASE function usage in SELECT list for data formatting & translation
SELECT ProductID, ProductName=Name,
ListPrice = CASE WHEN ListPrice > 0
THEN '$'+CONVERT(varchar(32),ListPrice,1) -- currency format
ELSE '' END,
ProductLine = CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'General'
ELSE 'Internal part'
END
FROM AdventureWorks2008.Production.Product
ORDER BY ProductLine, ProductName;
/* ProductID ProductName ListPrice ProductLine
....
330 Touring End Caps Internal part
513 Touring Rim Internal part
879 All-Purpose Bike Stand $159.00 Mountain
878 Fender Set - Mountain $21.98 Mountain
863 Full-Finger Gloves, L $37.99 Mountain .... */
------------
-- SQL CASE function in WHERE clause for search - QUICK SYNTAX
DECLARE @Color varchar(16) = NULL -- input parameter
SELECT * FROM AdventureWorks2008.Production.Product
WHERE Color = CASE
WHEN @Color is not null THEN @Color
ELSE Color END
GO
-- (256 row(s) affected)
DECLARE @Color varchar(16) = 'Blue' -- input parameter
SELECT * FROM AdventureWorks2008.Production.Product
WHERE Color = CASE
WHEN @Color is not null THEN @Color
ELSE Color END
-- (26 row(s) affected)
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
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 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
------------
-- 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
-- SQL Server - Using the CASE function & ISNUMERIC function in ORDER BY clause
SELECT p.BusinessEntityID,
FullName = FirstName + ' ' + LastName,
City,
PostalCode
FROM AdventureWorks2008.Person.Person p
INNER JOIN AdventureWorks2008.Person.BusinessEntityAddress bea
ON bea.BusinessEntityID = p.BusinessEntityID
INNER JOIN AdventureWorks2008.Person.Address a
ON bea.AddressID = a.AddressID
ORDER BY CASE
WHEN ISNUMERIC(PostalCode) = 0 THEN 1
ELSE 2
END,
City,
FullName DESC
/* 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 CASE, 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 - 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
*/
-- 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
------------
No comments:
Post a Comment