Solved

Extracting Zip Code

Posted on 2006-11-28
10
241 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

808 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