Find Names Containing a Digit — Pattern Matching in SQL

The problem

Brightlane's product catalog team is reviewing items whose name carries a numeric component — a model number, size, or generation identifier.

Write a query to return the ID and name of every product whose name contains at least one digit.

Assumptions:

The products table has one row per product with an id and a name.
A qualifying product has a name containing one or more characters in the range 0 through 9.

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 ~ '[0-9]'

The shape

name ~ '[0-9]' uses a character class to mean "any digit," and the unanchored ~ operator looks for that class anywhere in the string. One pattern covers Zephyr 12, Sofa 3-Seater, and Picture Frame 8x10 without enumerating each digit separately.

Clause by clause

SELECT id, name returns the product ID and name for the catalog review.
FROM products reads every product.
WHERE name ~ '[0-9]' keeps rows whose name contains at least one character in the range 0 through 9. The square brackets define a character class; [0-9] is shorthand for "any one of these ten characters." Because the regex is not anchored, a single matching digit anywhere in the name is enough.

Why this and not `LIKE '%0%' OR LIKE '%1%' OR ... OR LIKE '%9%'`

The LIKE form is correct but unmaintainable. It expands "contains a digit" into ten separate substring predicates joined by OR. A character class collapses the same intent into three characters of pattern. Whenever the question is "contains any character from this set," reach for a regex with a character class rather than writing out the set as alternatives.

The trap

[0-9] matches a single digit. [0-9]+ matches one or more consecutive digits. For "contains at least one digit," both produce the same boolean answer, because if any digit exists, both expressions match somewhere in the string. The + quantifier matters when you start extracting the digits or anchoring the pattern; for a pure existence check, the bare class is enough.

You practiced ~ '[0-9]' — character-class regex matching, the right shape for 'contains any digit' without enumerating each one.