?
Solved

Extracting Zip Code

Posted on 2006-11-28
10
Medium Priority
?
245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 
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 400 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 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