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