Postgresql wildcard LIKE for any of a list of words

Going beyond with regular expressions

Flexible search patterns bring in PostgreSQL's POSIX regular expressions. Use the marvelous ~* operator for case-insensitive matching, and the humble ~ for preserving case sensitivity. Here's how you can find any word in a list, no stress on cases:

SELECT * FROM my_table WHERE my_column ~* ANY (ARRAY['word1', 'word2', 'word3']); 
-- 'word1' feels pretty on top, doesn't it?


Remember, dancing with regular expressions could pave the way for a speedier and graceful solution than juggling multiple LIKE conditions. So, keep calm and regex on.

Embracing the ANY array for efficiency

Many words in the world, and many in your dynamic list. So why choke your query with numerous LIKE conditions? Construct an array with ANY and breathe easy. This dynamic solution is much more efficient and scalable:

SELECT * FROM my_table WHERE my_column LIKE ANY (ARRAY[word_list]); 
-- Why make each word feel lonely with a separate LIKE, when they can party inside ANY?


In the illustrious query above, word_list is a stand-in for an array of your finest search terms. Avoid an avoidable marathon of multiple table scans that come knocking with separate LIKE conditions.

Full text search for the complex quests

Your search terms come in all shapes and flavors? Commission PostgreSQL's full text search. An efficient trailblazer designed to handle dynamic arrays of words:

SELECT * FROM my_table WHERE to_tsvector('english', my_column) @@ plainto_tsquery('english', 'word1 | word2 | word3');
-- And voila! 'word1', 'word2' and 'word3' all attend the party without a fuss.


The magic touch? my_column morphs into a tsvector and the searcher string into a tsquery, searching any of the coveted words for dinner tonight.

Venturing into SIMILAR TO

Lists of words with their quirky sibling patterns can be tamed with SIMILAR TO:

SELECT * FROM my_table WHERE my_column SIMILAR TO '%(word1|word2|word3)%'; 
-- It's like taking all words on a wild rollercoaster ride, with `SIMILAR TO` at the wheel!


Sit back and enjoy SQL standard pattern matching that feels akin to regular expressions.

Case-insensitive exploration

For case-insensitive comparisons, simply lowercase the varchar field and each trooper in the word list. Equal opportunity for all upper and lower cases:

SELECT * FROM my_table WHERE LOWER(my_column) LIKE ANY (ARRAY[LOWER('Word1'), LOWER('Word2'), LOWER('Word3')]);
-- It's a lowercase party and everyone's invited!


Performance tune-up

Dancing with a plethora of records? Indexes can make your queries fly. Make sure your columns are suited up with indexes, and analyze query plans for optimal performance. Sometimes, just sometimes, consider upgrading your PostgreSQL for a breath of fresh air with improved indexing techniques and cool new pattern matching functions.