LIKE — Marketing Cloud SQL reference

LIKE is how you match text patterns inside a WHERE clause. In MC SQL it works like T-SQL: % matches any sequence (including empty), _ matches exactly one character, comparisons are case-insensitive in most tenants. The trap is performance — a query with a leading % can't use an index, so what runs instantly against 10k rows can crawl against 1M.

Official syntax

-- Wildcards: % (any sequence) and _ (exactly one char)
WHERE EmailAddress LIKE '%@enterprise.com'        -- ends with
WHERE EmailAddress LIKE 'german.%'                -- starts with
WHERE EmailAddress LIKE '%@%.gov'                 -- contains @ + ends with .gov
WHERE EmailAddress LIKE 'g_rman@%'                -- single-char wildcard

-- NOT LIKE for the inverse
WHERE EmailAddress NOT LIKE '%@%.test'

-- ESCAPE for literal % or _
WHERE Topic LIKE '50\% off%' ESCAPE '\'
WHERE FieldName LIKE 'user\_id' ESCAPE '\'

-- Combined with other WHERE conditions
WHERE Status = 'Active'
  AND EmailAddress LIKE '%@enterprise.com'
  AND LastPurchase >= DATEADD(day, -90, GETDATE())

LIKE only operates on text columns (varchar / nvarchar). Comparing dates or numbers requires CAST first — and at that point you usually want = or a range comparison instead.

Reference:

• Salesforce Help — Supported T-SQL syntax ↗
• Salesforce Help — SQL queries for Data Views (filtering examples) ↗

What survives in production

Leading wildcards kill performance

LIKE 'prefix%' can use an index on the column (if one exists). LIKE '%suffix' and LIKE '%substring%' cannot — they have to scan every row. On a 50k-row Data Extension that's invisible. On a 5M-row DE pushing the 30-min timeout, it's the difference between Activity success and failure.

-- FAST — anchored prefix, optimizer can use an index on EmailAddress
WHERE EmailAddress LIKE 'support@%'

-- SLOW — leading wildcard forces a full scan
WHERE EmailAddress LIKE '%@enterprise.com'

-- WORST — wildcards on both sides, full scan + character-by-character match
WHERE EmailAddress LIKE '%enterprise%'

You can't always avoid leading wildcards (matching email domains, for instance, requires LIKE '%@domain.com'). When you can't avoid it, stage the filter first — run the LIKE against a smaller staged DE that already had cheaper filters applied (Status = 'Active', date ranges) so the expensive scan touches a fraction of the rows.

Don't use LIKE when = would work

LIKE 'gold' is identical in result to = 'gold' but slower — the optimizer treats LIKE as pattern matching even when there are no wildcards. If the comparison is an exact match against a literal, use =.

-- AT RISK — LIKE with no wildcards is just slow =
WHERE LoyaltyTier LIKE 'gold'

-- CORRECT
WHERE LoyaltyTier = 'gold'

The cost is small per row, but it compounds — every row in the source DE pays the pattern-matching overhead, even though the answer is just a string equality check.

Escape % and _ when matching literal text

If you're searching for a string that contains a literal % or _, the wildcard meaning takes over unless you escape. Pick an ESCAPE character (commonly \) and prefix.

-- BUG — % is interpreted as wildcard, matches everything starting with "50"
WHERE Subject LIKE '50% off%'

-- CORRECT — \% is the literal percent sign
WHERE Subject LIKE '50\% off%' ESCAPE '\'

-- Also for underscores in field names or codes
WHERE FieldName LIKE 'user\_id' ESCAPE '\'

This shows up most often when matching email subjects, promo codes, or AMPscript variable names — anywhere % or _ appears in real data.

LIKE for suppression patterns: stage and re-use

Domain-based suppression ("never email anyone at @competitor.com") is a LIKE '%@competitor.com' pattern. Don't run it inside every production query — stage it once, reuse the staged DE.

-- AT RISK — LIKE runs against the full subscriber list every send
SELECT s.SubscriberKey
FROM master_subscribers s
WHERE s.Status = 'Active'
  AND s.EmailAddress NOT LIKE '%@competitor.com'
  AND s.EmailAddress NOT LIKE '%@partner-private.com';

-- DURABLE — stage suppressed addresses once, then anti-join
INSERT INTO de_stg_domain_suppressed
SELECT SubscriberKey
FROM master_subscribers
WHERE EmailAddress LIKE '%@competitor.com'
   OR EmailAddress LIKE '%@partner-private.com';

SELECT s.SubscriberKey
FROM master_subscribers s
LEFT JOIN de_stg_domain_suppressed d
  ON s.SubscriberKey = d.SubscriberKey
WHERE s.Status = 'Active'
  AND d.SubscriberKey IS NULL;

The first version pays the LIKE cost on every Send. The second pays it once per snapshot run, then every Send is a fast anti-join. See JOIN for the anti-join pattern.

Quick decision

Use LIKE when:

• You're matching a text pattern, not an exact value.
• You can anchor the wildcard to one side (prefix or suffix matching).
• The data is small enough that the scan cost is acceptable.

Use = instead of LIKE when:

• The pattern has no wildcards. Always.

Use LEFT JOIN ... IS NULL instead of NOT LIKE when:

• You're suppressing many patterns and the source is large. Stage the suppression list once.

Use LIKE with caution when:

• The pattern starts with a wildcard (%suffix, %substring%) — stage upstream filters first.
• The data contains literal % or _ — use ESCAPE.
• The query has to work across tenants with potentially different case sensitivity — wrap in LOWER().

Related

• Basics — supported T-SQL subset
• WHERE — LIKE lives inside WHERE
• JOIN — anti-join pattern for suppression at scale
• MC SQL gotchas — see #5 for case sensitivity / NULL behavior

More reference pages incoming: CASE · INSERT INTO · String / Date / Numeric / Conversion / Aggregate / Null Functions · Style Guide.

Plus how-to snippets for common production debugging — email sends, value length, contact reach, etc.