Database - the best way, how to search names

Question

I am solving in these days following situation: In my DB table I have columns for name and surname.

On my page I have input for searching people, and I am struggling with a problem, how to search the name in database that is stored in two columns and I have the name as string ("Joe Green").

For example, in database I have followings:

Joe New
Joe Blue
Joe Green
Joe Francois Green

What could be the best way, how this problem to solve? I am currently working with MySQL database and Rails 3.

EDIT: Thank you guys for you replies, but I don't know, how to make the query in Rails 3 notation, is it possible to use "concat"?

Solution 1

if your database table engine is myISAM then use FULLTEXT search

first create FULLTEXT index by

CREATE FULLTEXT INDEX fx_name ON pages (name, surname)

then use below query to retrieve required data

SELECT  *
FROM    table
WHERE   MATCH(name,surname) AGAINST ('keyword' IN BOOLEAN MODE)

update

alternative:use CONCAT

first create index

CREATE INDEX users_firstandlast ON users(name, surname);

option1: repeat the CONCAT in your WHERE clause (because AS doesn't create a name you can use in the WHERE clause):

SELECT CONCAT(name, ' ', surname) as fullname 
FROM users 
WHERE CONCAT(name, ' ', surname) LIKE '%joe green%';

option 2:

SELECT CONCAT(name, ' ', surname) as fullname 
FROM users 
WHERE name LIKE '%joegreen%' or surname LIKE '%joegreen%';

More information

Solution 2

A lot of this would be significantly simpler if you had given some indication of what language is being used at the interface between the input and the database (since Rails is usually associated with Ruby I'll assume you mean that), however....

SELECT *
FROM yourtable
WHERE CONCAT(name, ' ', surname) 
      LIKE CONCAT('%',REPLACE(?,' ', '%'),'%')
ORDER BY LEVENSTEIN(CONCAT(name, ' ', surname), ?)

(where '?' is replaced by your search string, and the levenstein function is described here)

Performance will be poor - a better solution would be to split the string Ruby and attempt matches of varying combinations.

Solution 3

One way is:
* split user's input to words
* search each word in name and surname columns

Solution 4

you can use the sql server full text search which i do not recommend because of the performance is very bad ,or use the lucin .net which is more powerful and her are some links : http://incubator.apache.org/lucene.net/

http://msdn.microsoft.com/en-us/library/ms142571.aspx

mark as answered if it helps :)

Solution 5

first use the split function to split the string

namearr = name.split

namestr = ""
namearr.each do |i|
  namestr = namestr + i
end

then use the values in your query

stmt = "select * from mytable where CONCAT(firstname,surename)= " + namestr

View more solutions