Condition in SQL for searching full name with no specific order of first name or last name

How can I search for a column named fullname by filtering on firstname/lastname without specific order?

In the following Javascript example, the query is invalid 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 query can I use to find "Elon Musk" by searching the keyword "Musk Elon"?

NB: columns firstname and lastname also exist

javascriptmysqlsql

Answer №1

After much deliberation, I was able to find a solution to the issue with the help of some clever JavaScript code.

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

Answer №2

To perform a comprehensive search, you can utilize the FULLTEXT option with MATCH AGAINST:

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

Additionally, remember to establish a FULLTEXT search index on the fullname column.

Alternatively, you can break down the searchWord by spaces and use 

LIKE '%$(searchWordPart1)%' AND LIKE '%$(searchWordPart2)%'
, etc.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Alter the browser's URI without having to reload the page

Is there a way to change the browser URL (or URI) without refreshing the page using HTML5 and HTML5Shiv for IE? For example, if I am currently on http://www.example.com but want to transition to http://www.example.com/4f6gt without the need for a full pa ...

javascriptjqueryajaxhtmluri

Calculate the combined sum and alter the values of three input fields

There are three text boxes (testbox, testbox2, testbox3) that receive values from an input field, radio button selection, and checkbox. The correct values are displayed in testbox, testbox2, testbox3. Additionally, the sum of testbox, testbox2, testbox3 n ...

javascripthtml

Clicking on a button in the Shield UI Grid Toolbar will apply filters to

Currently, I am working with a grid that utilizes a template for the toolbar. In this grid, there is a column labeled "Status." My goal is to filter the rows so that only those where the Status equals Request to Reschedule, Cancelled, Office Call Required, ...

javascriptjqueryfilteringshieldui

Transferring the selected option from a drop-down menu to a PHP page using an HTML form

I'm currently working on developing an HTML form that will send the user's question and the selected topic to a PHP page. The topics are retrieved from a MySQL database using PHP. My goal is to submit the chosen topic value from a dropdown menu, ...

phphtmlmysqlforms

Find and conceal the object within the list that includes the term "Ice"

The teacher's assignment is to create a radio button filter that hides items with the word "Ice" in them, such as "Ice Cream" and "Iced Tea." Here is the current code I have been working on: <!DOCTYPE html> <html> <head> <me ...

javascriptjqueryhtmlfunctionjquery-ui

Check for pattern using JavaScript regular expression

Utilizing ng-pattern to validate a regular expression. The pattern must include 3 letters and 2 numbers in a group. For example: G-31SSD or G-EEE43 Currently, the pattern only matches the second example. ng-model="newGroup.groupCode" ng-pattern="/^&bso ...

javascriptregexangularjsregular-language

Unable to store simple HTML file using service worker

I am working on a webpage that displays a message when the user is offline. However, I am facing an issue with my service worker while trying to cache the page. The Chrome console always throws this error: service-worker.js?v=1:1 Uncaught (in promise) D ...

javascriptservice-worker

"Exploring the best way to retrieve the angular filter value within a Node.js environment

Currently, I am facing an issue with accessing the value in Node.js using req.body when it is stored in ng-model. The ng-model includes an AngularJS filter, and I need to retrieve this filtered value from the input field in Node.js through req.body. Below ...

javascriptangularjsnode.js

When scrolling down, the popup window shifts its position

I have implemented a popup window which displays a list on hover. It works perfectly on the default page, but when I scroll down, the popup also moves with the scrollbar. Below is the HTML code that creates the hidden popup list initially and shows it on ...

javascripthtmlcss

Designing a layout for a chat application that is structured from the bottom up

I'm currently in the process of designing a web application for a chat platform. I have access to an API that provides a list of messages: chatsite.com/api/thread/1/messages/ [ { "id": 2, "sender": { "id": 2, ...

javascripthtmlcssvue.jsflexbox

Creating dependent dropdown lists is a useful way to streamline data entry and ensure accuracy in your

I am looking to create a series of 4 connected dropdown lists, structured like this: District: <select id="district"> <option>Select a District</option> <option value="district1">dstrict1</optio ...

javascripthtmlgoogle-apps-script

What is the best way to retrieve properties from a different module in JavaScript?

I have a module named ProgressIndicator: ... export default class ProgressIndicator { constructor() { ... const indicatorGeometry = new THREE.PlaneGeometry(2, 2, 1, 1) const indicatorMaterial = new THREE.ShaderMate ...

javascriptclassmodulethree.js

What is the solution for displaying just a single panel?

Is there a way to ensure that only the hidden text is displayed when clicking on the button within each panel? Currently, both panels are being revealed simultaneously... import React, { useState } from "react"; import "./styles.css"; export default func ...

javascriptreactjs

Find out which way the user is scrolling in your React js application

I'm struggling to determine whether the scroll event is moving up or down, and I haven't been able to find a solution yet. import React, { useState, useEffect } from "react"; import { Link } from "react-router-dom"; const Nav ...

javascriptreactjsscrollevent-handling

Looking to display a div with both a plus and minus icon? Having trouble getting a div to show with left margin? Need assistance hiding or showing div text

Can someone please review this source code? Here is the demo link: http://jsfiddle.net/bala2024/nvR2S/40/ $('.expand').click(function(){ $(this).stop().animate({ width:'73%', height:'130px' }); $( ...

javascriptjqueryhtmlcss

Ajax calls for validations are failing to trigger a response from PHP

I am currently working on validating my signup form using PHP and AJAX, but unfortunately, I am not receiving any response value. Below is the AJAX code snippet I am using: <script type="text/JavaScript"> function frmValidation(str) { ...

javascriptphpajax

Tips for displaying a specific array in react JS using the map function

Hello everyone, I am currently working on creating a hotel webpage using React.js and I'm focusing on developing the facilities page. My goal is to display the description of the facilities based on the button that the user clicks. How can I achieve t ...

javascriptreactjsreact-bootstrap

MERN Stack deployment to Heroku: Remote rejected - unable to push changes to master branch (pre-receive hook declined)

Just a heads up: I've successfully deployed using this method around 3 times in the past, but now it seems to be failing. Could there have been an update with Heroku that's causing this issue? Not entirely sure... I'm attempting to push my ...

javascriptnode.jsgitexpressheroku

Utilizing AJAX to update database entries while effectively managing error handling

I am currently developing a website that relies on ajax calls to update the database. While everything is running smoothly on xammp, I have concerns about potential issues once the site goes live. I have a few questions: If an ajax call fails (even tho ...

phpmysqlajaxerror-handling

Tips for implementing resizable Material-UI Dialogs

I'm working on a project that involves creating a dialog box that can be resized and dragged. While I found steps in the Material-UI Dialog documentation on how to make it draggable, I'm still looking for information on how to make it resizable. ...

javascriptcssreactjsmaterial-ui