I have the following table in my database:

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...

CodePudding user response：

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%'

CodePudding user response：

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)%'