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 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use the SQL isnumeric function?

Execute the following T-SQL script in Query Editor to demonstrate the application of the isnumeric() and TRY_CONVERT functions to check for non-numeric zip (postal) codes like the ones used in Canada and United Kingdom:

USE AdventureWorks2012;
 
GO
-- TRY_CONVERT function is new to SQL Server 2012
SELECT DISTINCT City,
                StateProvinceCode,
                Country = cr.Name,
                PostalCode
FROM   Person.Address a
       INNER JOIN Person.StateProvince sp
         ON a.StateProvinceID = sp.StateProvinceID
       INNER JOIN Person.CountryRegion cr
         ON cr.CountryRegionCode = sp.CountryRegionCode
WHERE  TRY_CONVERT(INT, PostalCode) is NULL
ORDER  BY Country,
          StateProvinceCode,
          City;
-- (128 row(s) affected)
------------ 
 
USE AdventureWorks2008;
GO
SELECT DISTINCT
City,
StateProvinceCode,
Country = cr.Name,
PostalCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
INNER JOIN Person.CountryRegion cr
on cr.CountryRegionCode = sp.CountryRegionCode
WHERE ISNUMERIC(PostalCode)<> 1
ORDER BY Country, StateProvinceCode, City;

GO
------------
-- SQL ISALPHANUMERIC test
------------
-- SQL not alphanumeric string test - sql patindex pattern matching
SELECT DISTINCT LastName
FROM   AdventureWorks2008.Person.Person
WHERE  PATINDEX('%[^A-Za-z0-9]%',LastName) > 0
GO
/* Partial results
 
LastName
Ansman-Wolfe
Barreto de Mattos
Ben-Sachar
Costa Da Silva
De Matos Miranda Filho
*/
-- SQL ALPHANUMERIC test - isAlphaNumeric
SELECT DISTINCT LastName
FROM   AdventureWorks2008.Person.Person
WHERE  PATINDEX('%[^A-Za-z0-9]%',LastName)= 0
GO
/* Partial results
LastName
Abbas
Abel
Abercrombie
*/

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

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