SQL condition on full name with no firstname/lastname order-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.