Validate Canadian Postal Code

DROP FUNCTION dbo.udf_IsValidCanadianPostalCode

GO

CREATE FUNCTION dbo.udf_IsValidCanadianPostalCode (@PostalCode nvarchar(10))

/******************************************************************************

** Name: IsValidCanadianPostalCode

** Desc: Check the formatting of a postal code matches Canadian standards.

**

** Output Parameters:

** @Result: 1 for valid Canadian Postal Code

** 0 for invalid Canadian Postal Code

**

** Auth: Paul Schlieper

** Date: 15 Jan-2007

**

** Date: 20 Jun 2012 - ChillyDBA - Changed function name and return codes to

** make the function purpose and result more obvious

*******************************************************************************/

RETURNS INTEGER

AS

BEGIN

/* D, F, I, O, Q, and U never used in Canadian postal codes

** Too easily mistaken for 0, 1, E, V by machine readers.

** Also, W and Z are never the first letter in the code.

*/

RETURN (SELECT CASE

WHEN LEFT(@PostalCode, 1) IN ('W', 'Z') THEN 0

WHEN LOWER(REPLACE(@PostalCode, ' ', '')) LIKE '[a-z][0-9][a-z][0-9][a-z][0-9]'

AND PATINDEX('%[dfioqu]%', @PostalCode) = 0 THEN 1

ELSE 0

END

)

END

--GO

/*

-- test

-- return 1 for success

select dbo.udf_IsValidCanadianPostalCode('A1B2C3')

select dbo.udf_IsValidCanadianPostalCode('H4E 5G6')

-- return 0 for failure

select dbo.udf_IsValidCanadianPostalCode('90210')

select dbo.udf_IsValidCanadianPostalCode('A11A1A1')

select dbo.udf_IsValidCanadianPostalCode('W4E 5G6')

*/