0 Replies - 1417 Views - Last Post: 14 July 2008 - 07:03 PM

#1 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

MSSQL user defined function to validate Canidian postal code

Posted 14 July 2008 - 07:03 PM

Description: Works with SQL 2000 and SQL 2005I had to create a function to validate a Canadian postal code for a project I'm working on, this is what I came up with
CREATE FUNCTION dbo.ValidateCanadianPostalCode (@postal_code VARCHAR(10))
RETURNS INTEGER
AS
   BEGIN
	/*
		Rules for Canadian Postal Code:

		D, F, I, O, Q, U are never used (too easily mistaken by readers)
		W & Z are never the first letters

		If a valid postal code 0 (zero) is returned
		else 1 is returned
	*/
	RETURN 
		(SELECT 
			CASE 
				WHEN LEFT(@postal_code, 1) IN ('W', 'Z') THEN 1
				WHEN LOWER(REPLACE(@postal_code, ' ', '')) LIKE '[a-z][0-9][a-z][0-9][a-z][0-9]' 
				AND PATINDEX('%[dfioqu]%', @postal_code) = 0 THEN 0
			ELSE 1 	
		END)
   END



Is This A Good Question/Topic? 0
  • +

Page 1 of 1