Sql query for searching through firstName and lastName columns for all similar records

Question

I am working on to build a query in MYSQL by which I can search through firstName and lastName columns in table that can return all records that nearly matches it.

Like I want to search a name say, Aditya Kumar Sharma and my table containes something like this:

ID    firstName        lastName
1     Jack             Mathews
2     Aditya           Singh
3     James            Hopes
4     Aditya Kumar     Sharma
5     Adityahappy      Singh

Now I want all the records containing any of these Aditya or Kumar or Sharma in both firstName and lastName columns, which means I need following records in my resultset:

ID    firstName        lastName
2     Aditya           Singh
4     Aditya Kumar     Sharma
5     Adityahappy      Singh

Can anyone provide me some clue, how to get it done. and it will be more pleasing if the most matching record comes on top, that is:

ID    firstName        lastName
4     Aditya Kumar     Sharma

comes on top of resultset, as it is completely matching the query String.

EDIT : I am using InnoDB engine for my database so FullText do not work over that, is there any other way around or I am forced to switch my DB engine?

He want all the records containing any of these Aditya or Kumar or Sharma in both firstName and lastName columns, And you are that means we need to consider or condition here not'And'. — Java, Mar 31 '12 at 14:25

safarov · Accepted Answer · 2012-03-31T11:58:47.367

2

I think FULLTEXT search will work

SELECT *, 
     MATCH ( `FirstName`, `Lastname`) 
     AGAINST ('+name +middle_name +lastname' IN BOOLEAN MODE) as `rel`
FROM `table` 
WHERE MATCH ( `FirstName`, `Lastname`) 
     AGAINST ('+name +middle_name +lastname' IN BOOLEAN MODE)
ORDER BY `rel` DESC

edited Mar 31 '12 at 11:58

answered Mar 31 '12 at 11:36

safarov

7,793
2
36
52

score 1 · Answer 2 · answered Mar 31 '12 at 11:32

You should try a MySQL FULLTEXT search which should help you. If you add a FULLTEXT index on firstName and lastName you can then query against it like this:

SELECT *,MATCH(fullNameIndex) AGAINST ('search term') AS relevance
FROM `table`
ORDER BY relevance DESC

And it should even give you the best match first.

score 1 · Answer 3 · answered Mar 31 '12 at 11:33

the string-function SOUNDEX converts a string into an sound-code (could be used to generate spoken words).

Examples:

mysql> select soundex('text'), soundex('test');
+-----------------+-----------------+
| soundex('text') | soundex('test') |
+-----------------+-----------------+
| T230            | T230            |
+-----------------+-----------------+

You can use the result of this function to compare strings

score 1 · Answer 4 · edited May 23 '17 at 10:24

1

You have to use MySQL Full-text Searching inbuilt function. See here for explanation with examples. Try this query.

If you are using MyISAM Engine.

 SELECT *FROM Emps
    WHERE MATCH(firstName,lastName) AGAINST ('Aditya Kumar Sharma' IN BOOLEAN MODE)

For Innodb , you have to spent some time with some of these expert solutions. Plz go through Link1 Link2 will help you.

edited May 23 '17 at 10:24

Community

1
1

answered Mar 31 '12 at 11:55

Java

2,451
10
48
85

but I am having InnoDB engine instead of MyISAM, and as far as I know Fulltext do not work on InnoDB, is there any other way around? – Abhinav Mar 31 '12 at 12:33
plz go through links that I have posted in my updated answer, will help you. – Java Mar 31 '12 at 12:44
Thanks for the links, they are indeed very helpful. – Abhinav Mar 31 '12 at 12:55

score 0 · Answer 5 · answered Mar 31 '12 at 11:37

0

http://en.wikipedia.org/wiki/Levenshtein_distance

I used this to great effectiveness once identifying addresses in a large data file that were similar. We also turned it to matching names and got terrific results.

answered Mar 31 '12 at 11:37

Wes Howard

23
1
5

Sql query for searching through firstName and lastName columns for all similar records

5 Answers5