CODEWITHSUNDEEP
mysqlsearchsql-like
ilo
ilo

Reputation: 312

Selecting rows with more than 1 LIKE

My problem here is that, I want to create a simple search engine. I've got it to work by using 2 queries. The first one searches for the full search term and the second one searches for all of the matches word by word. For example if You search for "The long lasting i don't know", then it will search for "The long lasting i don't know", then for "The", then "long", and so on.

But I want to use pagination and It would mess up the results, so I need it in 1 query. Is it achieveable with the IN operator?

Upvotes: 1

Views: 116

Answers (3)

Kio Coan
Kio Coan

Reputation: 581

You could try using REGEXP as said by @jazkat at Mysql Like multiple values

Your query would be:

SELECT * FROM table WHERE column REGEXP "The long lasting i don't know|The|long|lasting|i|don't|know"

More about REGEXP here: http://www.tutorialspoint.com/mysql/mysql-regexps.htm

Upvotes: 1

Arnaud Mongrain
Arnaud Mongrain

Reputation: 301

My answer may not be optimal on the performance side, but here's the idea I got.

You could search only with the word by word (column like '%The%' or column like '%long% etc.). You would surely get the results containing the whole sentence.

Then, you can order it with a case

Order By Case When Column Like "The long lasting i don't know%" Then 1 Other 2 End

Upvotes: 1

Tushar Kulkarni
Tushar Kulkarni

Reputation: 323

You have to use individual like with ORs as follows

select * from tablename where field like "%The long lasting i don't know%" or field like "%The%" or field like "%long%" or field like "%lasting%" or field like "%i%" or field like "%don't%" or field like "%know%"

refer this link Using SQL LIKE and IN together

Upvotes: 1

Related Questions