Solved

Extracting Zip Code

Posted on 2006-11-28
10
238 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now