Tuesday, January 27, 2015

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


Source : sqlusa

No comments:

Post a Comment