Postal Code Format check


select Postal_Code,count(*) from client where length(postal_code) = 6 and Postal_Code not like '[A-CEGHJ-NPRSTV-Z][0-9][A-Z] [0-9][A-Z][0-9]' group by Postal_Code order by 2 desc


IsNumeric(substring(Postal_code, 2, 1))

IsNumeric(substring(Postal_code, 5, 1))

IsNumeric(substring(Postal_code, 7, 1))



select Postal_Code,count(*) from client

where length(Postal_Code) = 7

and Not(IsNumeric(substr(Postal_code, 1, 1))) = 'True'

 and IsNumeric(substr(Postal_code, 2, 1)) = 'True'

and Not(IsNumeric(substr(Postal_code, 3, 1))) = 'True'

and substr(Postal_code, 4, 1) = ' '

and IsNumeric(substr(Postal_code, 5, 1)) = 'True'

and Not(IsNumeric(substr(Postal_code, 6, 1))) = 'True'

and IsNumeric(substr(Postal_code, 7, 1)) = 'True'

group by Postal_Code order by 2 desc



CREATE OR REPLACE TRIGGER EAS_PRV_CNTY_PCODE_CHK_CLI

AFTER INSERT ON CLIENT

REFERENCING NEW AS N

FOR EACH ROW

MODE DB2SQL

BEGIN ATOMIC

DECLARE vMSG VARCHAR(70);

IF N.PROVINCE NOT IN

('AL','AK','AB','AZ','AR','BC','CA','CO','CT','DE','DC','FL','GA','ID','IL','IN','IA','KS','KY','LA','ME','MB','MD','MA','MI','MN','MS','MO','MT','NE','NV','NB','NH','NJ','NM','NY','NL','NC','ND','NT','NS','NU','OH','OK','ON','OR','PA','PE','QC','RI','SK','SC','SD','TX','TN','UT','VT','VA','WA','WV','WI','WY','YT') THEN

SET vMSG = 'PLEASE USE STATE  / PROVINCIAL 2 LETTER DESIGNATION!';

 ELSE IF N.COUNTRY NOT IN ('CA','US') THEN

 SET vMSG = 'PLEASE FILL IN COUNTRY CODE FOR PROFILE!';

  ELSE IF N.COUNTRY = 'CA' AND LENGTH(POSTAL_CODE) <> 7 THEN

  SET vMSG = 'CANADA POSTAL CODES ARE 7 CHRACRTERS LONG, SPACE INCLUDED!';

   ELSE IF N.COUNTRY = 'CA' AND LENGTH(POSTAL_CODE) = 7 AND

    (

    Not(IsNumeric(substr(Postal_code, 1, 1))) = 'False'

    or IsNumeric(substr(Postal_code, 2, 1)) = 'False'

    or Not(IsNumeric(substr(Postal_code, 3, 1))) = 'False'

    or Not( substr(Postal_code, 4, 1) = ' ')

    or IsNumeric(substr(Postal_code, 5, 1)) = 'False'

    or Not(IsNumeric(substr(Postal_code, 6, 1))) = 'False'

    or IsNumeric(substr(Postal_code, 7, 1)) = 'False'

    ) THEN

   SET vMSG = 'PLEASE CHECK POSTAL CODE FORMAT. MUST BE A1A 1A1!';

   END IF;

  END IF;

 END IF;

END IF;

IF Length(vMSG) > 1 THEN

SIGNAL SQLSTATE '75100' SET MESSAGE_TEXT = vMSG;

END IF;

END