LIKE for pattern matching

Concepts covered: Pattern Matching

Equality asks whether a string is exactly one value. LIKE asks whether a string fits a pattern, where 2 wildcard characters stand in for text you do not know. The percent sign matches any run of characters, including a run of none, and the underscore matches exactly 1 character. Every other character in the pattern must match itself, the pattern must account for the whole string, and letter case counts.

The listings table below holds 6 titles. The 3 tabs search it with a prefix pattern, a contains pattern, and a pattern whose first character is a wildcard.

Prefix

Titles that begin with Lake, with anything or nothing after it.

SELECT
  listing_id,
  title,
  city
FROM listings
WHERE title LIKE 'Lake%'

Contains

Titles with Lake anywhere, still case sensitive.

SELECT
  listing_id,
  title,
  city
FROM listings
WHERE title LIKE '%Lake%'

Any first

Titles whose second to fourth characters are ake, whatever the first character is.

SELECT
  listing_id,
  title,
  city
FROM listings
WHERE title LIKE '_ake%'

The prefix pattern returns 3 rows, and listing 6 is one of them: the title 'Lake' leaves nothing after the prefix, and % matches nothing without complaint. The contains pattern adds listing 3, where Lake sits mid-title, for 4 rows. The third pattern also returns 4 rows but swaps listing 3 for listing 4, because the underscore accepts the lower case l that the capital L in the other 2 patterns rejected. The underscore accepts the D in 'Downtown Lake Studio' too, but the next 3 characters are own where the pattern requires ake, and the Lake later in the title does not help, because the pattern has no leading %.

Against the 6-row listings table above, how many rows does WHERE title LIKE 'Lake_%' return?

• 2 rows: Lakeview Cabin and Lakeside Loft
• 3 rows: Lakeview Cabin, Lakeside Loft and Lake
• 4 rows: every title containing Lake or lake
• 1 row: Lakeview Cabin, the longest match

Matching a literal wildcard

A discount label such as '50% off' contains a percent sign that should match itself. Name an escape character with ESCAPE and put it before the wildcard: label LIKE '50\%%' ESCAPE '\' reads the first % as a literal percent sign and the second as a wildcard, so it matches labels starting with 50%. The same trick covers underscores in codes like 'SKU_'.

What LIKE costs

A pattern with a fixed prefix lets an engine reading sorted data skip whole blocks whose values fall outside that prefix's range. A pattern that starts with %, such as '%Lake%', gives no prefix to skip by, so every row's title is read and compared. On a table of 200 million listings that is 200 million string comparisons for one search box. Pair a leading wildcard with other conditions, such as a city, so the engine filters by those first and runs the pattern on far fewer rows.

NOT LIKE keeps the rows that fail the pattern. A NULL title fits neither LIKE nor NOT LIKE, since comparing NULL to a pattern yields NULL, so the row disappears from both result sets. A table where 3 titles are NULL and 10 match the pattern returns 10 for LIKE and the remaining non-NULL titles for NOT LIKE, with the 3 NULL rows in neither.

Arrange the tiles into a query that returns the titles of Tahoe listings whose title contains the word Cabin, in alphabetical order. Two tiles do not belong.

About this lesson

This section is part of the Filtering: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.