Ada Lovelace: How to use the ISNUMERIC & CASE functions ?

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')

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

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

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

-- (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

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

-- Demonstration of IsNumeric for selected values

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

USE tempdb;

CREATE TABLE IsNumericDemo (

ID smallint identity primary key,

NumberString varchar(32),

[IsNumeric] bit )

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')

-- Set the IsNumeric flag

UPDATE IsNumericDemo SET [IsNumeric] = ISNUMERIC(NumberString)

-- Check results

SELECT * FROM IsNumericDemo

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

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

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

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

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

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

-- 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()

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'

-- SQL string to numeric conversion fails without validation

SELECT PurchaseOrderID, SubTotal = CAST (stringSubTotal as money)

FROM NumberValidation

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

/* Results

PurchaseOrderID SubTotal

2815 525.00

340 7623.00

-- SQL drop table

DROP TABLE NumberValidation

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

-- 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()

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

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

CREATE PROCEDURE uspDelta @PowerOf2 INT,

@Parm DECIMAL(38,3) OUTPUT

BEGIN

SET @Parm = POWER(CONVERT(BIGINT,2), @PowerOf2)+0.789

RETURN (ISNUMERIC(CAST (@Parm as varchar)))

END

-- Execute stored procedure - Outputs: @Isnumeric, @DecimalParm

DECLARE @DecimalParm DECIMAL(38,3), @IsNumeric tinyint;

EXEC @IsNumeric = uspDelta 50, @DecimalParm OUTPUT;

SELECT IsItNumeric = @IsNumeric, sprocResult = @DecimalParm;

/* Result

IsItNumeric sprocResult

1 1125899906842624.789

-- Cleanup

DROP PROC uspDelta

Source : sqlusa

Ada Lovelace

Tuesday, January 27, 2015

How to use the ISNUMERIC & CASE functions ?

No comments:

Post a Comment