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

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

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

*/

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