Cool Tips‎ > ‎Data Validation‎ > ‎

Data Validation - Validate Canadian Postal Code

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Paul Schlieper
Date:        15 Jan 2007

Description

This is a scalar function that will validate the correctness of a Canadian Postal code string.
I changed the return code logic and function name from Paul's original to better indicate the purpose and result
 
    Return Code 1 = Valid Canadian Postal Code
    Return Code 0 = Invalid Canadian Postal Code 

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

*/


ċ
udf_IsValidCanadiaPostalCode.sql
(2k)
Andy Hughes,
Jun 23, 2012, 1:42 AM
Comments