CODEWITHSUNDEEP
mysqlfuzzy-search
mwild
mwild

Reputation: 1655

Partial/Abbreviated Name Matching

Im trying to write a query that will match partial matches to stored name values.

My database looks as follows

Blockquote

FirstName | Middle Name | Surname
----------------------------------
Joe       | James       | Bloggs
J         | J           | Bloggs
Joe       |             | Bloggs
Jane      |             | Bloggs

Now if a user enters their name as

J Bloggs

my query should return all 4 rows, as they are all potential matches.

Similarly if a user enters the name

J J Bloggs

all rows should be returned.

If a user enters their name as 

Joe Bloggs

only the first three should be returned.

I have tried the following

SELECT * 
FROM PERSON 
WHERE CONCAT(' ',FirstName,' ',MiddleName,' ', Surname) LIKE '% Joe%'
    AND CONCAT(' ',FirstName,' ',MiddleName,' ', Surname, ' ') LIKE '% Bloggs%';

But this doesn't return 'J J Bloggs'.

Any ideas?

Upvotes: 2

Views: 87

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

If I understand your logic correctly, any of the three input name components is considered to be a match if it either is a substring of a value in the table, or vice-versa. That is, J matches Joe, but also Joe matches to J. Using this logic, we can write the following query:

SELECT *
FROM yourTable
WHERE
    (INSTR(FirstName, 'J') > 0 OR INSTR('J', FirstName) > 0) AND
    (INSTR(MiddleName, 'J') > 0 OR INSTR('J', MiddleName) > 0 OR MiddleName IS NULL) AND
    (INSTR(Surname, 'Bloggs') > 0 OR INSTR('Bloggs', Surname) > 0);

Demo

Note that the middle name has some additional logic. If the middle name be missing in a record (i.e. it is NULL), then we wave the requirement for the middle names to match.

Upvotes: 1

Barry
Barry

Reputation: 3318

I think you might need OR instead of AND...

SELECT * 
FROM PERSON 
WHERE CONCAT(' ',FirstName,' ',MiddleName,' ', Surname) LIKE '% Joe%'
    OR CONCAT(' ',FirstName,' ',MiddleName,' ', Surname, ' ') LIKE '% Bloggs%';

Upvotes: 0

Related Questions