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)