• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Extracting Zip Code

Hi,

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?

Thanks,
Shweta
0
Shweeta
Asked:
Shweeta
  • 5
  • 3
1 Solution
 
GRayLCommented:
Does this do it?

SELECT 3 as Source, a.add3, substr(a.addr3, instr (a.addr3, '-')-5, 10) AS ZipCode FROM Addresses AS a
WHERE a.add3 LIKE "*[0-9][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]*"
UNION
SELECT 2 as Source, a.add2, substr(a.addr2, instr (a.addr2, '-')-5, 10) AS ZipCode FROM Addresses AS a
WHERE a.add2 LIKE "*[0-9][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]*"
UNION
SELECT 1 as Source, a.add1, substr(a.addr1, instr (a.addr1, '-')-5, 10) AS ZipCode FROM Addresses AS a
WHERE a.add1 LIKE "*[0-9][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]*";

0
 
GRayLCommented:
Sorry, remove the brackets from the hyphen:

SELECT 3 as Source, a.add3, substr(a.addr3, instr (a.addr3, '-')-5, 10) AS ZipCode FROM Addresses AS a
WHERE a.add3 LIKE "*[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]*"
UNION
SELECT 2 as Source, a.add2, substr(a.addr2, instr (a.addr2, '-')-5, 10) AS ZipCode FROM Addresses AS a
WHERE a.add2 LIKE "*[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]*"
UNION
SELECT 1 as Source, a.add1, substr(a.addr1, instr (a.addr1, '-')-5, 10) AS ZipCode FROM Addresses AS a
WHERE a.add1 LIKE "*[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]*";
0
 
ShweetaAuthor Commented:
This is fine, but this can result in duplicate rows.

I want it as - if I get a proper zip code in addr3, that should be the result for that row.

If not, then check in addr2, and if not even in addr2, then check in addr1.

If I get a zip code in addr3, I don't want to check in addr2.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
GRayLCommented:
Why would you have a zip code in two or more lines of a three line address?  Anyway to answer your question:

SELECT a.Name,
Iif(a.add3 LIKE "*[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]*",Mid(a.add3,Instr(a.add3,"-")-5,10),
Iif(a.add2 LIKE "*[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]*",Mid(a.add2,Instr(a.add2,"-")-5,10),
Iif(a.add1 LIKE "*[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]*",Mid(a.add1,Instr(a.add1,"-")-5,10),"NoZip")))
FROM myTable;
0
 
GRayLCommented:
Shweeta:  Problems?
0
 
ShweetaAuthor Commented:
This would work in most cases but in a situation where there exist two '-' in the string. One in the zip code, one before that, then this might fail.

Is there a way to match patterns in access queries?
0
 
ShweetaAuthor Commented:
Well, I wrote an oracle procedure to do it.. outside the access application because of the problem I told above.

i'll close this question in a day or two.
0
 
GRayLCommented:
>Is there a way to match patterns in access queries?< I thought I just showed you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now