Mysql query question - Partial match

virtuali1151
virtuali1151 used Ask the Experts™
on
I am trying to get fields that have partial matching addresses to match via different suffixes for addresses.. for example:

10 hideman Street would qualify as a match of 10 hidman St.

These are the suffixes I am working with so far:

s/street/st
road/rd
drive/dr
boulevard/blvd
avenue/ave

This is my query, but I need to know how to edit it to match the address fields with the above conditions:

SELECT
`group`.`Group ID`,
`group`.`Additional Address Line 1`,
smart.subs_acct_num,
smart.Address,
smart.mac_address
FROM
`group` ,
smart
WHERE
`group`.`Additional Address Line 1` = smart.Address
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Why not rationalise your database and standardise so you are using only one version of each.

First do a select to find out how many variations you have for instance

SELECT DISTINCT * FROM smart WHERE address LIKE '% St %'

Open in new window


This will tell you what will change if run an update query to change St => Street

Repeat for the rest.

We had a project dealing with over a million addresses from the retail sector. It took as a couple of days to do running various queries and script but we ended up with a DB that was 99% standardised - it made reporting significantly easier.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial