Search code examples
sqlsql-like

How to find records with multiple column values matching user input?

I have a table which contains patients information in it. 

First_Name    Last_Name 
TEST          DO NOT USE
LEES          ESTATE
LEES          ROSA

So, based on user input I need to search if First_Name or Last_Name matches with user input. If user entered multiple words, I should split the word and check each word against First_Name and Last_Name columns.

For example, if user input is "D N U", I need to get first row.

If user input is "ES" I need get all three rows. Similarly , If user input is "SA ES" I need get third rows.

I'm trying to use LIKE Operator, but I think there should be more logic applied to achieve what I require.

Solution

  • I suspect that something like this would work:

    where concat(last_name, ' ', first_name) like concat('%', replace($user_input, ' ', '%'), '%')

    The exact method for string concatenation varies by database.