What is the most efficient way to perform a case insensitive search for a word in a TEXT (or CLOB) field?

I've been using WHERE LOWER(body) LIKE '%word%' but I think the calls to LOWER() might be slowing the query significantly.

  • 3
    PostgreSQL or DB2? Furthermore, you can have an index on lower(body) but that won't help much with a search term starting with a wildcard. Look into full text search - and then you have at least two methids to compare for yourself. – dezso Sep 11 '15 at 14:26
  • I'm writing code for supporting both databases. Thanks for the input. – Laurent Sep 11 '15 at 14:35
  • 2
    In DB2 you cannot even create an index containing a LOB column, so at least in this case table scan is guaranteed. To perform full text search efficiently you have to use appropriate capabilities of each database, but, since they are not standard SQL, you will not be able to have a universal solution. – mustaccio Sep 12 '15 at 0:37
  • Please define "word" more closely. A word from the English dictionary? An arbitrary sequence of characters? A full-text search pattern? LIKE '%word%' indicates the latter. Provide version numbers of your software, your table definition, cardinality, column size, some sample values and the desired result. – Erwin Brandstetter Sep 12 '15 at 18:47
  • In older versions of DB2, running LOWER on a column (rather than the predicate) will indeed slow down your query. The better practice is to run the function over the predicate (especially since you can create indexes over functions on predicates in the newest version of DB2). Otherwise I would recommend either putting a lowercase copy of the data in another column of the same table, or researching the Text Search capabilities of DB2. However those will be specific to DB2 as mustaccio alluded to. – Chris Aldrich Sep 15 '15 at 12:41

In PostgreSQL, you can use Text Search:

create index on foo_table using gin ( to_tsvector('simple', body) );

from foo_table
  to_tsvector('simple', body) @@ plainto_tsquery('simple', 'word');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.