Regex Alternation in Product Names — Pattern Matching in SQL

The problem

Brightlane's product naming audit is identifying items whose name carries a generation or tier designation — Pro, Plus, or Max.

Write a query to return the ID and name of every product whose name contains Pro, Plus, or Max as written, with capitalization respected.

Assumptions:

The products table has one row per product with an id and a name.
A qualifying product has a name containing the exact-cased substring Pro, Plus, or Max somewhere in the string. Lowercase or other-case variants do not qualify.

Output:

One row per qualifying product, with columns id and name.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  name
FROM
  products
WHERE
  name ~ 'Pro|Plus|Max'

The shape

name ~ 'Pro|Plus|Max' is case-sensitive regex matching with alternation. The | means "or," so the single pattern Pro|Plus|Max accepts any string containing any one of the three substrings. Because POSIX regex is not anchored by default, the match can sit anywhere in the name, which is what Apex Titan 15 Pro and SoundPod Pro both need.

Clause by clause

SELECT id, name returns the product ID and name. The audit doesn't need any other column.
FROM products reads the catalog.
WHERE name ~ 'Pro|Plus|Max' keeps the rows where the name contains Pro, Plus, or Max as written. The ~ operator is case-sensitive, so a lowercase pro would not qualify, which is the prompt's "capitalization respected" condition.

Why this and not three `LIKE` conditions joined by `OR`

name LIKE '%Pro%' OR name LIKE '%Plus%' OR name LIKE '%Max%' returns the same rows. The regex form folds three predicates into one and reads as a list of acceptable substrings. For three alternatives the saving is modest; for ten or twenty the regex stays one line while the LIKE chain grows linearly. Either spelling is correct here. The point of reaching for ~ is that alternation is a first-class part of its pattern language.

The trap

Pro|Plus|Max is not anchored. It matches anywhere in the string, which is what this problem wants. Writing ^Pro|Plus|Max$ would mean "starts with Pro OR contains Plus OR ends with Max," because | has lower precedence than the anchors, not "starts and ends with one of the three." Grouping with parentheses, as in ^(Pro|Plus|Max)$, is how you bind the anchors across the alternation.

You practiced ~ 'a|b|c' — case-sensitive regex matching against multiple alternatives in a single pattern.

Return the ID and name of every product whose name contains `Pro`, `Plus`, or `Max` as written, with capitalization respected

The shape

Clause by clause

Why this and not three `LIKE` conditions joined by `OR`

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not three LIKE conditions joined by OR

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not three `LIKE` conditions joined by `OR`