Extracting Zip Code
Posted on 2006-11-28
I want to frame a sql query for extracting zip code.
I have 3 fields - addr1, addr2, addr3.
I want to search in the priority, addr3, addr2, addr1. The zip code should be of the format 99999-9999.
where 9 can be any digit.
I could search for one field like substr(addr3, instr (addr3, '-')-5, 10).
This would give me the extracted code. But now, I want to check if these are all numbers and if so, consider it as zip code. If not, I want to proceed with the same thing for addr2 and then addr1. How can i do it in a single query?