You could get the list of US postal codes (probably Canadian as well) and
just do a lookup to see if the code is valid. You could even use a hash
look up since the table will be fairly static. I believe you can get the
list for something like $20/quarter in electronic format on a CD.
Jim
Sebas Santos wrote in message <36BE8804.82EC7E98_at_shaw.wave.ca>...
>I created this two functions and the procedure. When I run I get the
>answers "function created" without errors, and procedure created without
>errors. However when I execute the procedure that calls the functions, I
>have a funny output error message. Can anyone help?
>Thanks in advance,
>Sebastian
>
>--FUNCTION TO CHECK FOR USA ZIP ONLY
>--
>CREATE OR REPLACE FUNCTION CHECK_ZIP (P_CODE IN VARCHAR2)
>RETURN NUMBER
>IS
>STR_IN VARCHAR2(7) := P_CODE;
>BEGIN
> FOR I IN 1..5 LOOP
> IF SUBSTR(STR_IN, 1, I) NOT BETWEEN 0 AND 9
> THEN
> RETURN 0;
> EXIT;
> END IF;
> END LOOP;
> RETURN 1;
>END;
>
>/*******************************/
>
>--FUNCTION TO CHECK FOR CANADIAN POSTAL CODE ONLY
>--
> CREATE OR REPLACE FUNCTION CHECK_POSTAL (P_CODE IN VARCHAR2)
> RETURN NUMBER
> IS
> STR_IN VARCHAR2(7) := UPPER(P_CODE);
> STR_2 VARCHAR2(6);
> BEGIN
> IF LENGTH(STR_IN) > 6 THEN
> STR_2 := SUBSTR(STR_IN, 1, 3)||SUBSTR(STR_IN, 4, 6);
> END IF;
> IF SUBSTR(STR_2, 1, 1) BETWEEN 'A' AND 'Z' THEN
> IF SUBSTR(STR_2, 1, 3) BETWEEN 'A' AND 'Z' THEN
> IF SUBSTR(STR_2, 1, 5) BETWEEN 'A' AND 'Z' THEN
> IF SUBSTR(STR_2, 1, 2) BETWEEN 0 AND 9 THEN
> IF SUBSTR(STR_2, 1, 4) BETWEEN 0 AND 9 THEN
> IF SUBSTR(STR_2, 1, 6) BETWEEN 0 AND 9 THEN
> RETURN 1;
> ELSE RETURN 0;
> END IF;
> END IF;
> END IF;
> END IF;
> END IF;
> END IF;
> END;
>
>
>/********************************/
>CREATE OR REPLACE PROCEDURE VAL_POSTAL (P_CODE IN VARCHAR2, P_STATUS OUT
>VARCHAR2)
>IS
>STR_IN VARCHAR2(10) := UPPER(P_CODE);
>CHECKER NUMBER;
>BEGIN
>IF LENGTH(STR_IN) = 5 THEN
> CHECKER := CHECK_ZIP(STR_IN);
> IF CHECKER = 1 THEN
> P_STATUS := 'THIS ZIP CODE IS VALID';
> ELSE
> P_STATUS := 'THIS ZIP CODE IS INVALID';
> END IF;
>--
>ELSIF LENGTH(STR_IN) BETWEEN 6 AND 7 THEN --CHECK FOR CANADIAN POSTAL
>CODE
> IF INSTR(STR_IN, 'O') = 1 THEN
> P_STATUS := 'THIS IS NOT A VALID POSTAL CODE';
> END IF;
> CHECKER := CHECK_POSTAL(STR_IN);
> IF CHECKER = 1 THEN
> P_STATUS := 'THIS POSTAL CODE IS VALID';
> ELSE
> P_STATUS := 'THIS POSTAL CODE IS INVALID';
> END IF;
>END IF;
>END;
>/*END OF THE VAL_POSTAL PROCEDURE
>
>
>
Received on Mon Feb 08 1999 - 07:19:12 CST
