Case Insensitive City Search — Pattern Matching in SQL

The problem

Brightlane's logistics system identifies customers in cities whose name contains new somewhere — a quick way to surface New York, Newcastle, New Orleans, and similar locations.

Write a query to return the ID, name, and city of every customer whose city contains new, regardless of capitalization.

Assumptions:

The customers table has one row per customer with an id, a name, and a city.
A qualifying customer has a city containing new somewhere in the string, with case ignored.

Output:

One row per qualifying customer, with columns id, name, and city.

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,
  city
FROM
  customers
WHERE
  city ILIKE '%new%'

The shape

ILIKE '%new%' matches the substring new anywhere inside the city name, regardless of capitalization. The leading and trailing % make the match positional-agnostic, and ILIKE folds the case so New York, new orleans, and NEWCASTLE all qualify under one pattern.

Clause by clause

SELECT id, name, city returns the three columns the logistics report needs. Including the matched city lets the reader confirm why each row was kept.
FROM customers reads the customer table.
WHERE city ILIKE '%new%' keeps the rows where the city contains new somewhere. With % on both sides, the substring can sit at any position; with ILIKE instead of LIKE, the column's casing does not have to match the pattern's casing.

The trap

% is the wildcard that allows surrounding characters. Without the leading %, the pattern ILIKE 'new%' would only match cities that start with new, dropping Newcastle if it were spelled differently and missing any city where the substring sits in the middle. Without the trailing %, the pattern would require the city to end on new. Both wildcards are load-bearing for an "anywhere in the string" match.

You practiced ILIKE '%pattern%' — case-insensitive substring matching across a column whose values may use varying capitalization conventions.

Return the ID, name, and city of every customer whose city contains `new`, regardless of capitalization

The shape

Clause by clause

The trap

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