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

Mysql – SQL condition on full name with no firstname/lastname order

Given a column named fullname, how can I filter on firstname/lastname with no order ?

In the example below in javascript, the query is not valid when searching by lastname

function getQuery(searchWord){
  return `SELECT * FROM user WHERE fullname like '%${searchWord}%' `
}
// trying to search fullname "Elon Musk"
getQuery("Elon M") // okay
getQuery("Musk E") // no result

What is the query that allow me to find "Elon Musk" by searching by keyword "Musk Elon" ?

NB: columns firstname and lastname exists as well

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Finally i resolved the problem by using some javascript.

      function getQuery(searchTerm = '') {
    // " Musk  E " => ["Musk", "E"]
    const arr = searchTerm.split(/s+/).filter((e) => e)
    // ["Musk", "E"] => (first_name like '%Musk%' or  last_name like '%Musk%') AND (first_name like '%E%' or  last_name like '%E%')
    const conditions = arr
      .map((e) => `(first_name like '%${e}%' or  last_name like '%${e}%')`)
      .join(` AND `)
    return `use SNOW select distinct sys_id, name from dbo.sys_user where ${conditions}`
  }

  2. 0

    You can do a FULLTEXT search using MATCH AGAINST:

    SELECT * FROM user WHERE (MATCH (fullname) AGAINST ('${searchWord} IN NATURAL LANGUAGE MODE'))

    And you have to create a FULLTEXT search index on the column fullname

    Or you can split your searchWord by space and do LIKE '%$(searchWordPart1)%' AND LIKE '%$(searchWordPart2)%' etc.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search