Solved

Extracting Zip Code

Posted on 2006-11-28
10
240 Views
Last Modified: 2008-03-10
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
Comment
Question by:Shweeta
  • 5
  • 3
10 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 18031407
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
 
LVL 44

Expert Comment

by:GRayL
ID: 18031599
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
 
LVL 1

Author Comment

by:Shweeta
ID: 18032144
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 44

Expert Comment

by:GRayL
ID: 18033258
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
 
LVL 44

Accepted Solution

by:
GRayL earned 100 total points
ID: 18049497
Shweeta:  Problems?
0
 
LVL 1

Author Comment

by:Shweeta
ID: 18056846
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
 
LVL 1

Author Comment

by:Shweeta
ID: 18109372
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
 
LVL 44

Expert Comment

by:GRayL
ID: 18109402
>Is there a way to match patterns in access queries?< I thought I just showed you.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now