Question 114795 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Search for multiple values in multiple columns in SQL

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

3

Answers


  1. Chosen as BEST ANSWER
    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.


  2. 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%'
    Login or Signup to reply.
  3. 0

    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)%'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search