Joe Celko's SQL for Smarties, 3rd Edition

12.5. CASE Expressions and LIKE Predicates

The CASE expression in Standard SQL lets the programmer use the LIKE predicate in some interesting ways. The simplest example is counting the number of times a particular string appears inside another string. Assume that text_col is CHAR(25) and we want the count of a particular string, ‘term’, within it.

SELECT text_col,
       CASE
       WHEN text_col LIKE '%term%term%term%term%term%term%'
       THEN 6
       WHEN text_col LIKE '%term%term%term%term%term%'
       THEN 5
       WHEN text_col LIKE '%term%term%term%term%'
       THEN 4
       WHEN text_col LIKE '%term%term%term%'
       THEN 3
       WHEN text_col LIKE '%term%term%'
       THEN 2
       WHEN text_col LIKE '%term%'
       THEN 1
       ELSE 0 END AS term_tally
  FROM Foobar
 WHERE text_col LIKE '%term%';

This depends on the fact that ...