Parsing Question

[R Bolling]

I have one field with data as follows:

107 N. Stephens Dr.
N. Stephens St.
Stephens Pl.

I need to break it up in three fields so that it looks as follows:

StNum Dir StName
107 N. Stephens Dr.
N. Stephens St.
Stephens Pl.

Can anyone suggest a query parsing routine that will do this trick?

Thanks for any help

Robbie Bollinger

 

[RobFMS]

Parsing is one of the major pains when it comes to addresses.

I don't know that you will find a magic answer. Users enter addresses
information so inconsistently, that its difficult to find the scheme for all
possible occurrences.

How would you (on paper) handle the following (i.e. spaces are intentional):

# 101 North Smith Drive
#101 N. Smith Dr.
North Smith Drive, Suite #101
North Smith Drive, Suite # 101

Are these possible senarios that you might encounter?

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Al Campagna]

Robbie,
I have to agree with Rob's response. There simply is no "definite" logic
to addresses, so parsing them is a monumental, if not impossible task.
Tell you what... have you called the post office to see if they might
have a program that would parse your addresses. I know they take normal
name and address databases, and massage them so they all conform to the
legitimate PO addressing scheme. I don't know, but I'd try that... they
might be of help.

But.... I'd really be interested to know why it's necessary to parse the
address field at all. I've never run into a name/address database with the
address broken up into all those fields.

 

[R Bolling]

I was hoping that I would be able to put together a query that was
more concise, but I ended up with the following. So far works as
expected:

SELECT CA.CAaddress,
IIf(IsNumeric(Mid([CAaddress],1,1))=True,Mid([CAaddress],1,InStr(1,[CAaddress],"
")),"") AS StNum, IIf(InStr(1,[CAaddress],"
N.")>0,"N.",IIf(InStr(1,[CAaddress]," N
")>0,"N.",IIf(InStr(1,[CAaddress],"
S.")>0,"S.",IIf(InStr(1,[CAaddress]," S
")>0,"S.",IIf(InStr(1,[CAaddress],"
E.")>0,"E.",IIf(InStr(1,[CAaddress]," E
")>0,"E.",IIf(InStr(1,[CAaddress],"
W.")>0,"W.",IIf(InStr(1,[CAaddress]," W ")>0,"W.","")))))))) AS
Direction, LTrim(IIf(Mid(LTrim(Mid([CAAddress],(Len(Trim([StNum])))+(Len(Trim([Direction]))+1),99)),1,1)=".",Mid(LTrim(Mid([CAAddress],(Len(Trim([StNum])))+(Len(Trim([Direction]))+1),99)),2,99),LTrim(Mid([CAAddress],(Len(Trim([StNum])))+(Len(Trim([Direction]))+1),99))))
AS Address
FROM CA;

Nothing like the challenge of real work