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

*/