Canadian Postal Code validator

,

This is a slight improvement on the script posted the other day. Uses PATINDEX for pattern match, and checks for forbidden characters D, F, I, O, Q, U.

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]

--GO
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
**
**      Auth: Paul Schlieper
**      Date: 15 Jan-2007
*******************************************************************************/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 -1
WHEN LOWER(REPLACE(@PostalCode, ' ', '')) LIKE '[a-z][0-9][a-z][0-9][a-z][0-9]'
AND PATINDEX('%[dfioqu]%', @PostalCode) = 0
THEN 0
ELSE -1 
END)
   END
--GO

/*
-- test

-- return 0 for success
select dbo.CheckPostalCodeFormat('A1B2C3')
select dbo.CheckPostalCodeFormat('H4E 5G6')
-- return -1 for failure
select dbo.CheckPostalCodeFormat('90210')
select dbo.CheckPostalCodeFormat('A11A1A1')
select dbo.CheckPostalCodeFormat('W4E 5G6')

*/

Rate

5 (1)

Share

Share

Rate

5 (1)