CODEWITHSUNDEEP
mysqlsql
cute_programmer
cute_programmer

Reputation: 372

How to use LIKE operator in sql query that returns data that didn't match exactly the value but has a few words in it

I have a book titled "Web Developer", "Web Designer" and I want a query that will return both book when I searched anything that's has "web" word in it for example "Web Consultant". But the problem is when I query like this it's not returning any data

SELECT * FROM `books` WHERE `title` LIKE '%Web Consultant%';

but I know changing the like value to '%web%' will return all data with "web" world in it. But my problem is what if the user add some words after the "web" word.

So the question is what is the exact approach or query for doing it, thanks!

Example: the scenario here is what if a user is searching for something that he can't remember the full title but only a word in it. So he will try to search like "web blahblablah" so the goal here is I want to return all books that has a "web" word on their title's

Upvotes: 0

Views: 586

Answers (3)

Nick Tsai
Nick Tsai

Reputation: 4129

I suggest to handle the input before query:

  1. Explode the input to an array by blank. PHP for example:

    $wordArray = explode(' ', 'Web Consultant');

  2. Combine each word into OR conditions to a query:

    SELECT * FROM books WHERE title LIKE '%Web%' OR title LIKE '%Consultant%';

This is a simple pattern what search engine does.

In your case, you could also define the first word to 'Web%', which makes more accurate for finding a title pattern.

Upvotes: 0

Jeff Holt
Jeff Holt

Reputation: 3190

what if the user add[s] some words after the "web" word

If you want a list of books whose title contains "web" followed by some unknown word, then you can execute something this:

SELECT * FROM `books` WHERE `title` REGEXP 'web[[:blank]]+[[:alpha:]]+'

Of course, it depends on how you define word as to what follows the + in the above RE. But I think the point of your question is that you want to exclude books whose title ends with "web" because you know that there must be some word after it. You can look here for more details on how you can define word.

You should keep in mind that you asked to find "web" followed by some unknown word. If you had said you wanted to find the word web followed by some unknown word, then the RE would be more complex. You would have to allow the "w" to be either at the beginning of the title or be preceded by a whitespace character.

Upvotes: 1

Gilbert
Gilbert

Reputation: 3776

Regular expressions to the rescue, though the details vary across implementations. I've been using Oracle lately, but reviewing MySql, if you don't need multi-byte characters, I come up with:

SELECT * FROM `books` WHERE title REGEXP 'Web Developer|Web Designer'

https://dev.mysql.com/doc/refman/5.5/en/regexp.html

Upvotes: 1

Related Questions