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

Postgresql – SQL query to find exact words in any order

I have a postgres table Article which has three columns – title, description, categories. All the three columns contain plain text.

I have 2 search words – say word1, word2

I want to find the records where title+description+categories contains all the exact search words, in any order.

  • need exact case-insensitive matches.
    Ex. man shouldn’t match against woman.
  • The search words can appear in any order Ex. If we search for "pollution global warming" – it should match against "… global abc warming adds to toxic mix of pollution…"

Need help in implementing a procedure(if not possible by query) where we can pass in the list of search words and it should return the list of records.

Any pointer will be a great help.

On a side note, I intend to consume this in django. If this requirement can be better achieved using django ORM, that should also suffice.

Tags:

2

Answers


  1. 0

    You can do:

    select *
from (select lower(title || description || categories) as txt from t)
where txt like lower('%word1%') and txt like lower('%word2%')
    Login or Signup to reply.
  2. 0

    Well considering that you don’t want them in a specific order, you can match syntax by concatenating the two columns into a new column and then checking the condition for both of them.

    SELECT * 
FROM (SELECT LOWER(CONCAT(col1, ' ', col2)) AS conc_col from table)
WHERE conc_col LIKE LOWER('%word1%') AND conc_col LIKE LOWER('%word2%')

    Notice that using AND will only return the string if both the words are present.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search