Link to home
Start Free TrialLog in
Avatar of virtuali1151
virtuali1151

asked on

Mysql query question - Partial match

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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image
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.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.