Technical Article

Validating Canadian Postal Codes


For fellow Canadians and neighbours to the south, you may find useful a function to validate the formatting of a Canadian postal code.  More than once I've had to work with web programmers on checking that a postal code meets the A1A1A1 style, so carry around a function that simply returns a true/false bit if a submitted varchar value does not meet our Canadian postal code standard.  I hope you find it useful!  Thanks to a colleague Patrick M. for quality checking this script.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CheckPostalCodeFormat]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CheckPostalCodeFormat]

CREATE FUNCTION dbo.CheckPostalCodeFormat (@PostalCode nvarchar(10))
**      Name: CheckPostalCodeFormat
**      Desc: Check the formatting of a postal code matches Canadian standards.
**      Output Parameters:
**          @Result:  0 for no errors
**                   -1 for an error
**      Note:
**          input value is automatically uppercased with spaces removed, 
**          more stringent checking could be applied.
**      Auth: Ryan Brochez
**      Date: Nov-20-2006
*******************************************************************************/RETURNS bit

    DECLARE @Result bit
    DECLARE @i int

    SET @Result = 0
    SET @i = 1
    SET @PostalCode = Upper(Replace(LTrim(@PostalCode), ' ', ''))

    -- Check the length
    IF Len(@PostalCode) <> 6 SET @Result = -1

    -- Character is alphanumerical (in the right position)
        WHILE (@i <= 6) 
            -- first character is alphabetical
            IF (@i%2 = 1) AND SubString(@PostalCode, @i, 1) NOT BETWEEN 'A' AND 'Z' 
                SET @Result = -1
            -- second character is numerical
            ELSE IF (@i%2 = 0) AND SubString(@PostalCode, @i, 1) NOT BETWEEN '0' AND '9' 
                SET @Result = -1    
            SET @i = @i + 1

    RETURN @Result


-- test

-- return 0 for success 
select dbo.CheckPostalCodeFormat('A1B2C3')
select dbo.CheckPostalCodeFormat('d4E 5f6')

-- return -1 for failure
select dbo.CheckPostalCodeFormat('90210')
select dbo.CheckPostalCodeFormat('A11A1A1')



You rated this post out of 5. Change rating




You rated this post out of 5. Change rating