Question 113528 in Mysql Question posted in
The official MySQL documentation can be found here.

MySql partial match based on part of the query code

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

Tags:

2

Answers


  1. 0

    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:
    enter image description here

    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`;

    enter image description here

    Login or Signup to reply.
  2. 0

    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
";
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search