Joe Celko's SQL for Smarties, 3rd Edition

12.4. Avoiding the LIKE Predicate with a Join

Beginners often want to write something similar to <string> IN LIKE (<pattern list>) rather than a string of ORed LIKE predicates. That syntax is illegal, but you can get the same results with a table of patterns and a join.

CREATE TABLE Patterns
(template VARCHAR(10) NOT NULL PRIMARY KEY);

INSERT INTO Patterns
VALUES ('Celko%'),
       ('Chelko%'),
       ('Cilko%'),
       ('Selko%),
       ('Silko%');

SELECT A1.lastname
  FROM Patterns AS P1, Authors AS A1
 WHERE A1.lastname LIKE P1.template;

This idea can be generalized to find strings that differ from a pattern by one position without actually using a LIKE predicate. First, assume that we have a table of sequential numbers and these following tables with sample data.

-- the ...