Search for multiple values in multiple columns in SQL

Question

I have the following table in my database:

firstName	lastName
Kalinda	Hemms
Karolyn	Greenwood
Aaron	Walework
Michel	Jurka

Now I want to create a query with multiple strings that should return only the records with that string. For example, searching for ka, should return Kalinda, Karolyn, and Michel. This is simple and I am achieving this with

SELECT * FROM table WHERE firstName LIKE '%ka%' OR lastName LIKE '%ka'

Now how do I create a query when my search is ka he? I expect to get back only Kalinda and Michel, since both values ka and he are present in their names. The biggest problem I am having is that the amount of values is unknown.

Actually I am working with JPA, but any help is welcomed.

WHAT I TRIED

I am making one query for each value, concatenating the results, and removing duplicates. But this brings me back Kalinda, Karolyn, and Michel. Karolyn is not supposed to be there since she does not have the value he in her name. Also multiple queries is not the best for the database...

EDIT AFTER SOME ANSWERS

After looking at some answers that helped me (hopefully) in the right direction, what I am looking for is a way to replace the | operator for an AND operator:

SELECT * FROM table WHERE CONCAT(firstName, lastName) SIMILAR TO '%(he|ka)%'

Answer 1

I was finally able to find a solution using regex and the ~ operator of PostgreSQL:

SELECT * FROM table WHERE LOWER(CONCAT(firstName, ' ', lastName)) ~ '^(?=.*he)(?=.*ka).*$'

For more values I just have to add (?=.*value) to the regex.

Answer 2

For PostgreSQL, there is a great feature called similar to.

Simply, you can search different keys using a regex pattern. For example:

select * from table where firstName similar to '%(ka|he)%'

In your case, you can also look lastName because you want to search both.

So, you may check following query and this SQLFiddler link:

select * from mytable where lower(firstName) similar to '%(he|ka)%' OR lower(lastName) similar to '%(he|ka)%'

Answer 3

So if I'm understanding correctly, you want to get everyone that has both strings somewhere in their full name.

Would this work ?

SELECT * FROM table WHERE firstName+' '+lastName LIKE '%ka%' AND firstName+' '+lastName LIKE '%he%'