DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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

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

Related articles:

ISNUMERIC (Transact-SQL)

Problem with ISNUMERIC

How to use the CASE expression for report formatting?

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE