Using LIKE to search for a name

Question

I have a table which contains contact information. This table has 1 column relevant to names. It looks like this:

Sort Name:
Doe, John
Clinton, Bill
Dooby Doo, Scooby

Sadly, there are no first name / last name columns, and they can't be added. If a user enters the search term "john doe", is there any way I can get mysql to return the column for "Doe, John" as my result?

Edit: As a follow up to the answer by RHSeeger, what would I do to make

john doe 

into

$name(0=>'john', 1=>'doe')

using php

Thanks for the help so far

Answer 1

In your application, split the search term at the last space, change the order and add a comma in between.

Answer 2

I would begin by parsing the search into separable words "john" and "doe" then then write a search using the LIKE functionality for both terms.

EDIT: To answer your question from a answer below... how do parse "John Doe" into "John" and "Doe" I would write a MySQL stored procedure to separate them into a result set containing "John" and "Doe" and then use the like versus that column name

Answer 3

Look in to the sql "like" operator. For example

select * from TABLENAME
where sort_name like '%doe'

You would need to do a little manipulation to the search terms on the application side and possibly supply other conditional clauses in your sql statement with other like clauses.

Answer 4

My thought would be:

SELECT * FROM tablename
WHERE LOWER(sort_name) LIKE '%name1%'
  ...
  AND LOWER(sort_name) LIKE '%nameN%'

where <name1> ... <nameN> are the individual, lowercased "words" (split on space, comma?) in the name the user requested.

Edit: It's worth noting that, for a reasonably large sized data set, searches like this are going to be slow. If you're going to have lots of data, you could consider a search engine (like SOLR) that you use to search for things, and using MySQL just for lookup by ID.

Answer 5

have a full-text index on the names column and then do this,

select * from names where match(name) against ('john doe');