Address Search TEXT

[altijani]

Greetings Community,

I have the following data (HAVE), and would like to match between two different addresses that our customers have.

Street Number	Street Name	Address City	State	ZIP	Address 2
12	MAIN STREET	GREENSBORO	NC	12345	12 MAIN ST, GREENSBORO NC 12345
56	SIDE ROAD	RALEIGH	NC	54231	56 SIDE RD, RALEIGH NC 54231
78	WALNUT BLVD.	CARY	NC	12300	WALNUT BOLUVARD 78, CARY NC12300
90	SIDE PL.	IVY	NC	12340	PO BOX 123, IVY NC 12340
34	PAUL GARDENS	APEX	NC	12350	PAUL GRDNS. 34 APT C1, APEX NC 12350

I need an additional column to specify whether the two addresses are the same in at least BOTH the "Street Number" and the "Street Name", otherwise it is not a match:

Street Number	Street Name	Address City	State	ZIP	Address 2	Match
12	MAIN STREET	GREENSBORO	NC	12345	12 MAIN ST, GREENSBORO NC 12345	YES
56	SIDE ROAD	RALEIGH	NC	54231	56 SIDE RD, RALEIGH NC 54321	YES
78	WALNUT BLVD.	CARY	NC	12300	WALNUT BOLUVARD 78, CARY NC12300	YES
90	SIDE PL.	IVY	NC	12340	PO BOX 123, IVY NC 12340	NO
34	PAUL GARDENS	APEX	NC	12350	PAUL GRDNS. 34 APT C1, APEX NC 12350	YES

Many thanks!

[LaurieF]

The comparison bit is easy: catx(' ', street_number, street_name) = address_2.

What's hard, but not hugely, is to find the street-type identifier (street, road, boulevard, grove, lane, alley; usually the last word) and standardise it on both sides. My method is to abbreviate it in both places (street -> st, grove -> gv) and strip out all punctuation, including commas and full-stops. I'd also recommend setting them both to upper or lower case.

I had initial problems when I was working in England with a town called Street, but because the meaning of the words isn't important, the match worked perfectly.