MySql partial match based on part of the query code

Question

In MySql, is there any way of getting a partial match if you use something like:

SELECT * FROM table WHERE name LIKE '%orange llp%';

And the output to be something like:

+-----------------+
| name            | 
+-----------------+
| Orange          |
| Telecomm Orange |
| Orange SA       | 
| Orange LLP      | 
+-----------------+

So even if the query is not an exact string to get matches based on just part of it? Hopefully, this makes sense.

EDIT: I'm using this with a nodejs & express backend so the query will be done automatically by the backend

user3Ayy · Answer

Since you updated that you are not using php se below

var searchQuery = "orange llp";
var splitQuery = searchQuery.split(" ");
var sqlQuery = "SELECT * FROM table WHERE ( name";
for(i = 0; i < splitQuery.length; i++){
  var query = splitQuery[i];
  sqlQuery += " LIKE '%" + query + "%' OR name ";
}
sqlQuery += " LIKE '%" + searchQuery + "%' )";

//Now run your query like below
con.query(sqlQuery);

Your output query will look like this

SELECT * FROM table WHERE ( name LIKE '%orange%' OR name  LIKE '%llp%' OR name  LIKE '%orange llp%' )

Use this class php search controller for php version

 $searchQuery = "orange llp";
 $search = new SearchController(SearchController::OR);
 $search->setOperators(SearchController::HAVE_ANY_QUERY);
 $search->setQuery($searchQuery)->split();
 $search->setParameter(array('name', /*more columns to search on*/));

 $sqlQuery = "SELECT * FROM table {$search->getQuery()}";

You can also add other clues

$sqlQuery = "
  SELECT * FROM table {$search->getQuery()}
  AND id = anything else
";

FUZIION · Answer

I think the issue is that you do not have any `'s around your table name and column name, and you don't order the output (no idea if this is a requirement, but it works for me so..)

SELECT * FROM `table` WHERE `name` LIKE '%orange llp%' ORDER BY `id`;

If you want to select multiple values, try it like this:

SELECT * FROM `table`
WHERE `name` LIKE '%word1%'
  OR `name` LIKE '%word2%'
  OR `name` LIKE '%word3%'
ORDER BY `id`;

Image:

I just tested this within a random database in my phpmyadmin and it functioned as expected.

SELECT * FROM `adminnotification_inbox` WHERE `description` LIKE '%to imp%' ORDER BY `notification_id`;

MySql partial match based on part of the query code

2 Answers