How to make an dynamic SQL SELECT query by passing in a comma separated list of values?

I have a column in my SQL database (using mySQL) ‘lastname’, which has any number of employee’s last names in it. I want to write a query that handles a search for last names using a comma delimited list.

So the user will type:

Garcia, Smith, Jones

And that gets stored in a String, lastNameQuery, which then gets passed into my backend API function that calls the SQL command. I want to return all employees in the DB that have those last names.

Is there any kind of SQL SELECT command I can use that search using a list like that? For my other functions (which only handle a single search term) I’m using this:

"SELECT * FROM employees WHERE salary LIKE '%${salary}%'"

Which works great. Is there some way I can modify it to handle a list? I can always break up the single String ("Garcia, Smith, Jones") into an array if necessary so that’s not a problem. Any ideas?

Answer

You need to either do:

(lastname like '%Garcia%' or lastname like '%Smith%' or lastname like '%jones%')

or create a fulltext index on lastname (alter table employess add fulltext (lastname)) which would let you do

match (lastname) against ('Garcia, Smith, Jones')

but won’t do things like find Garcia if you search for just “Garc”.