Zip Code

[cranebill]

I am validating data for a table.

I have a column I use to mark that a row should not be used.

One of the fields I need to validate is zipcode.

I know probably need to build a procedure or function that checks to see whether the zip is completely numeric for USA residents, or a combination of alpha numeric for Canadian, we have a column to distinguish country. If either of these are false or the field contains symbols etc then it needs to populate the elimination flag.

Any one have some pointers?

 

[feherke]

Hi

If this is what you want :

[gray]-- all[/gray]
[blue]SQL>[/blue] [b]select[/b] * [b]from[/b] cranebill;

ZIP        COUNTRY
---------- ----------
1234       US
123x       US
1234       CA
123x       CA
123^       US
123^       CA

[gray]-- good[/gray]
[blue]SQL>[/blue] [b]select[/b] * [b]from[/b] cranebill [b]where[/b] translate(zip,decode(country,[i]'US'[/i],[i]'0123456789'[/i],[i]'CA'[/i]
,[i]'0123456789abcdefghijklmnopqrstuvwxyz'[/i]),[i]'.'[/i]) [b]is null[/b];

ZIP        COUNTRY
---------- ----------
1234       US
1234       CA
123x       CA

[gray]-- bad[/gray]
[blue]SQL>[/blue] [b]select[/b] * [b]from[/b] cranebill [b]where[/b] translate(zip,decode(country,[i]'US'[/i],[i]'0123456789'[/i],[i]'CA'[/i]
,[i]'0123456789abcdefghijklmnopqrstuvwxyz'[/i]),[i]'.'[/i]) [b]is not null[/b];

ZIP        COUNTRY
---------- ----------
123x       US
123^       US
123^       CA

Just use the expression in an [tt]update[/tt] statement :

[blue]SQL>[/blue] [b]update[/b] cranebill [b]set[/b] bad=[i]'b'[/i] [b]where[/b] translate(zip,decode(country,[i]'US'[/i],[i]'0123456789'[/i],[i]'CA'[/i]
,[i]'0123456789abcdefghijklmnopqrstuvwxyz'[/i]),[i]'.'[/i]) [b]is not null[/b];

Feherke.

http://rootshell.be/~feherke/

 

[cranebill]

This is close, however "1234 CA" is not a valid zip as canada contains both alpha and numeric. I will look into the translate function as I do not have alot of experience with it.

 

[feherke]

Hi

Oops. I understood that Canadian zip code may contain letters, but not must.

Feherke.

http://rootshell.be/~feherke/

 

[cranebill]

I used this and am able to search for a particular pattern:

translate(zip_postal,'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ', '----------**************************')