0

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)%'
Improve this question
4
  • What's your database? Commented Sep 6, 2022 at 10:08
  • @stuck PostgreSQL Commented Sep 6, 2022 at 10:11
  • "I expect to get back only Karolyn and Michel" I think you meant Kalinda and not Karolyn, because he is not present in Karolyn's last name Commented Sep 6, 2022 at 11:18
  • Thanks @EvgeniyChekan. You are absolutely right. I changed the text. Commented Sep 6, 2022 at 11:45

3 Answers 3

Reset to default
1

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)%'
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Problem with this solution is that SIMILAR TO accepts only two alternatives, hence a query with '%(he|ka|s)%' will not work.
Why two? The string part is just a regular expression; it should work.
Yor're right. Was a typo in my query... But it still does not work perfectly. In your fiddle, three records are returned, but Karolyn should not be part of the result, since she don't have hein her name...
Oh, I see; you want both ka and he not one of them. In this case, just change the query with AND instead of OR
Will also not work, because then one of the values has to be in each column. So if I am looking just for ka it will fail. Also keep in mind that in reality the table has more columns like department, mail, etc.
|
0

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%'
Improve this answer

3 Comments

not really, because what if the user wants to search for ka he s or ka he s a? Keep in mind that in reality the table has more columns like department, mail, etc.
Oh I see, I didn't understand that it had to be dynamic depending on a space separated list. In that case can you use T-SQL for this ? You could dynamically create your SQL query by splitting the search query in T-SQL and concatenate the conditions to a query string that you would then execute.
T-SQL is valid only for Microsoft SQL. I am using PostgreSQL
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.