How can I use LIKE with an arbitrary word order in MySQL?

Question

I need to use the LIKE keyword or some other similar operator for this purpose:

In the database, the name and surname is stored as one column and it's in the order surname name (e.g. "Doe John").

I have a search bar with lets you enter your name and surname. When I am going to search, I require that if you enter name surname instead of surname name (e.g. if you enter "John Doe"), I still want the same results to show up as if you had written surname name.

How can I achieve this?

Answer 1

//in PHP
//after filtering and validating name and surname
$where = 'WHERE name = ' .$name. ' ' .$surname. ' OR name = ' .$surname. ' ' .$name;

Check for both.

Answer 2

SELECT SUBSTRING_INDEX(name, ' ', -1) SUBSTRING_INDEX(name, ' ', 1)

Negative value as the count (3rd variable) returns everything right of the space. Positive values return everything to the left.

This is assuming you have a single field where you input both values.

Answer 3

You could split the search input at whitespace and search for each word separately. You'd have to write a simple function to generate the statement. Basically, if you'd search for:

Luke Skywalker

The statement you'd get would be:

SELECT * FROM `table`
WHERE
    (`name` LIKE "Luke%" OR `name` LIKE "Skywalker%")
    AND
    (`surname` LIKE "Luke%" OR `surname` LIKE "Skywalker%")

If someone searches for three words (or one word only) you'd have three (or one) LIKE statements per field. And if you want to be less strict and you only require one of the words to match, replace the AND with an OR.

Answer 4

I don't know what language you are using but the context/main idea should stay the same. You could add 2 text boxes, 1 for name and 1 for surname. Then you should use a LIKE query that searches for your text box name and surname value in that order.

EDIT:

A second way is to split up the content of the textbox and save them in 2 separate strings. Then search for it using something like this:

SELECT * FROM <your table> WHERE names LIKE 'name1+" "+name2' OR WHERE names LIKE 'name2+" "+name1';

Answer 5

If your table is MyISAM, you can create a FULLTEXT index on both fields:

CREATE FULLTEXT INDEX ON mytable (name, surname)

and issue this query:

SELECT  *
FROM    mytable
WHERE   MATCH(name, surname) AGAINST ('+John +Doe' IN BOOLEAN MODE)

This will return both John Doe and Doe John.

On a MyISAM table this query will work even without creating the index, however, in this case it will be much more slow.

Update:

This will work even if you have name and surname in a single field:

CREATE TABLE t_name (id INT NOT NULL PRIMARY KEY, name VARCHAR(200) NOT NULL) ENGINE=MyISAM;

INSERT
INTO    t_name
VALUES
(1, 'John Doe'),
(2, 'Doe John');

SELECT  *
FROM    t_name
WHERE   MATCH(name) AGAINST ('+John +Doe' IN BOOLEAN MODE);

1, 'John Doe'
2, 'Doe John'

Update 2:

On an InnoDB table, you can use regular expressions:

SELECT  *
FROM    t_name
WHERE   name RLIKE '[[:<:]]John[[:>:]]'
        AND name RLIKE '[[:<:]]Doe[[:>:]]'

Answer 6

Try this

Original Data:

name

Doe John
Stack Overflow
Pew Ned

Record to search

declare @String2Search varchar(50)
set @String2Search = 'John Doe'

Sql Sever Query

select name from @t 
where REPLACE(name,' ','') like '%' + REPLACE(@String2Search,' ','') + '%'
OR
SUBSTRING(name,CHARINDEX(' ',name),len(name)) + SUBSTRING(name,0,CHARINDEX(' ',name))  like '%' + REPLACE(@String2Search,' ','') + '%'

MySql Query

select name from @t 
where REPLACE(name,' ','') like '%' + REPLACE(@String2Search,' ','') + '%'
OR 

SUBSTRING(name,LOCATE(' ',name),LENGTH(name)) + SUBSTRING(name,0,LOCATE(' ',name))  like '%' + REPLACE(@String2Search,' ','') + '%'

Note- I am a sql server guy. so first I wrote the query in SQL SERVER and then I checked in google for the corresponding function equivalence in MYSQL and I just replaced those

like

SQLSERVER ---------------------------------------------- MYSQL



SUBSTRING() ------------Equivalent----------------------- SUBSTRING() 



CHARINDEX() ------------Equivalent----------------------- LOCATE()


REPLACE() --------------Equivalent----------------------- REPLACE()


LEN() --------------Equivalent----------------------- LENGTH()

In Sql Server it is working fine. The concept I implemented is I am matching the word from the search text against First Name and Last Name as well as the reverse (Last Name & First Name)

I have used Replace function to remove any spaces between the charecters

This statement(in sql server)

SUBSTRING(name,CHARINDEX(' ',name),len(name)) + SUBSTRING(name,0,CHARINDEX(' ',name))

OR

in MY SQL

SUBSTRING(name,LOCATE(' ',name),LENGTH(name)) + SUBSTRING(name,0,LOCATE(' ',name))

is basically converting the First and LastName to LastName followed by First Name

Hope you got the concept and it may help you.

Answer 7

SELECT * FROM t_name WHERE name LIKE '%john%' AND name LIKE '%doe%';