Skip to content

LIKE Operator

Goal: Match text by pattern using LIKE and wildcards.

Wildcards

Wildcard Matches
% Zero or more characters
_ Exactly one character

Examples:

NAME LIKE 'A%'         -- starts with A
NAME LIKE '%ville'     -- ends with "ville"
NAME LIKE '%ville%'    -- contains "ville" anywhere
NAME LIKE 'A_lanta'    -- A, any single char, lanta → "Atlanta"
NAME LIKE '____'       -- exactly 4 characters

Esri's older style used * and ?

Older ArcGIS docs reference * and ? as wildcards. In modern ArcGIS Pro with file geodatabases, use % and _ (SQL standard). Pro auto-translates in some places, but writing the SQL standard is safer.

Case sensitivity

Depends on the data source:

Source Default
File geodatabase Case-sensitive
Shapefile Case-sensitive
SQL Server Usually case-insensitive
PostgreSQL Case-sensitive (use ILIKE for insensitive)

To make file-gdb queries case-insensitive:

UPPER(NAME) LIKE 'A%'

NOT LIKE

NAME NOT LIKE 'A%'    -- doesn't start with A

Escaping the wildcards

If you need to match a literal % or _, use the ESCAPE clause:

DESC LIKE '50\%' ESCAPE '\'         -- match the literal "50%"
NAME LIKE 'a\_b' ESCAPE '\'         -- match "a_b" exactly

Practical patterns

Pattern Use case
'St %' Starts with "St " (saint streets)
'%St' Ends with "St"
'% Inc.' Ends with " Inc."
'Atl%' "Atlanta", "Atlas", "Atlantic"…
'%@gmail.com' Gmail addresses
'404-%-____' "404-XXX-XXXX" phone format
'%[0-9]%' (Some dialects only) — contains a digit

Combining with other conditions

NAME LIKE 'A%' AND POP > 50000
NAME LIKE '%ville' OR NAME LIKE '%burg'
COUNTY = 'Fulton' AND ADDRESS LIKE '%Peachtree%'

A common gotcha

NAME LIKE 'Atlanta'     -- ❌ no wildcards = same as NAME = 'Atlanta'

LIKE without a wildcard behaves exactly like =. Probably not what you want.

Performance note

LIKE 'A%' (anchored at the start) can use an index → fast.

LIKE '%ville' (wildcard at the start) cannot use an index → slow on big tables. For huge enterprise databases, consider full-text indexes.

Practice

Try these

Counties layer:

  1. Counties starting with "M" → NAME LIKE 'M%'
  2. Counties ending with "County" (most do) → NAME LIKE '%County'
  3. Counties containing "land" → NAME LIKE '%land%'
  4. 4-letter county names → NAME LIKE '____'
  5. Not starting with a vowel → NAME NOT LIKE 'A%' AND NAME NOT LIKE 'E%' AND NAME NOT LIKE 'I%' AND NAME NOT LIKE 'O%' AND NAME NOT LIKE 'U%'

→ Next: AND / OR.