LIKE and NOT LIKE predicates in DB2 SQL

LIKE syntax

The form is match-expression [NOT] LIKE pattern-expression [ESCAPE escape-expression]. The match expression is the string you test (usually a column). The pattern is a constant, host variable, special register, allowed scalar function, CAST, concatenation, or array element, with a documented maximum pattern length (4000 bytes). Match, pattern, and escape must all be character or graphic strings (or a mix of those) or all binary strings. Distinct types must be cast to their source type first.

1
2
3
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE LASTNAME LIKE '%SMITH%';

IBM’s example: this predicate is true when NAME is SMITH, NESMITH, SMITHSON, or NESMITHY. It is not true for SMYTHE—the letters must match the non-wildcard parts exactly. Db2 LIKE is not case-insensitive. If data is stored in mixed case, either store a search column in a consistent case or apply a function (knowing that UPPER(LASTNAME) LIKE 'SMITH%' may not match an index on LASTNAME).

Evaluation rules (beginner version of the manual)

• If match or pattern is null, LIKE is UNKNOWN.
• If both are empty, LIKE is TRUE.
• Empty match and a non-empty pattern: not a match unless the pattern is only percent signs.
• Non-empty match and empty pattern: FALSE.
• Otherwise TRUE when the match can be partitioned to fit the pattern’s substring specifiers.

Redundant percents do not change the meaning: AB%%%%CD is equivalent to AB%CD.

Wildcard patterns you will actually write

• Prefix — LIKE 'JO%' names that start with JO (JOSEPH, JONES). This is the index-friendly shape.
• Suffix — LIKE '%SON' names that end with SON. Leading % usually cannot match an index.
• Contains — LIKE '%SMITH%' substring search. Flexible and often expensive.
• Single character — LIKE 'JON_S' matches JONES or JONAS (one character in the fourth position), not JONS (too short) and not JONNES (too many characters there).
• Fixed mask — account patterns like '____X%' (four characters, then X, then anything).

1
2
3
4
5
6
7
8
-- Prefix
WHERE LASTNAME LIKE 'JO%'
 
-- One wildcard character
WHERE LASTNAME LIKE 'JO_ES'
 
-- Contains (watch the access path)
WHERE LASTNAME LIKE '%BERG%';

NOT LIKE

m NOT LIKE p is equivalent to NOT (m LIKE p). Rows that clearly fail the pattern are kept. Rows where LIKE is UNKNOWN (null column or null pattern) stay UNKNOWN, so WHERE still drops them.

1
2
3
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE LASTNAME NOT LIKE 'A%';

That keeps non-null last names that do not start with A. Null last names do not appear. NOT LIKE is typically not a matching index predicate. If you need “not starting with A” and the table is large, compare EXPLAIN with a range (LASTNAME < 'A' OR LASTNAME >= 'B' in the encoding you actually use—EBCDIC letter order is tricky) versus NOT LIKE.

ESCAPE

When the data itself contains % or _, those characters in the pattern would be wildcards unless you escape them. ESCAPE escape-expression names a single character (one SBCS or DBCS character, or one byte for binary). In the pattern, that character may appear only as:

• escape + % — literal percent
• escape + _ — literal underscore
• escape + escape — literal escape character

Any other use of the escape character in the pattern is an error. IBM’s table with escape +:

1
2
3
4
5
-- Host variable PATTERN contains: AB+_C_%
-- Escape is + so +_ is a literal underscore
SELECT EMPNO, LASTNAME
  FROM DSN8C10.EMP
 WHERE LASTNAME LIKE :PATTERN ESCAPE '+';

IBM’s walkthrough: if PATTERN is AB+_C_%, the predicate is true for AB_CD or AB_CDE, and false for AB, AB_, or AB_C. The first underscore is literal because of +; the second underscore is a wildcard; % eats the rest.

ESCAPE is not allowed when the match expression is mixed ASCII/EBCDIC data. Unicode mixed (UTF-8) may use ESCAPE. If you search mixed EBCDIC columns for a literal %, you may need a different technique (LOCATE, POSSTR, or a generated column) rather than ESCAPE.

Host variables, CHAR padding, and parameter markers

If the pattern lives in a fixed-length host variable or parameter marker, trailing blanks are part of the pattern. IBM’s warning: CHAR(10) set to 'WYSE%' becomes 'WYSE% ' (percent plus five blanks). You then search for values that start with WYSE and end with five blanks, unless LIKE blank-insignificant behavior is in effect. That is not a prefix search.

Fixes:

• Use a VARCHAR host variable whose actual length is the pattern length.
• If the language only has fixed CHAR, make the variable the exact pattern length, or pad with extra % characters (IBM suggests 'WYSE%%%%%%' when you meant “starts with WYSE”).
• Never assume MOVE of a short literal into PIC X(100) is a valid LIKE prefix pattern.

Pattern expressions built with concatenation are allowed. Keep the result within 4000 bytes. Do not concatenate unsanitized user text into dynamic SQL; use a host variable for the pattern and keep the statement text static so the cache can reuse it.

Mixed data, Unicode, and binary

For mixed ASCII/EBCDIC data, an SBCS underscore matches one SBCS character, a DBCS underscore matches one MBCS character, and either percent matches zero or more SBCS or MBCS characters. EBCDIC redundant shift bytes are ignored. For Unicode, underscore matches one character and percent matches a string of characters; full-width and half-width % and _ have documented code points (UTF-8 / UTF-16).

Binary LIKE uses bytes. The special bytes are the binary percent and underscore, not the character glyphs you type in a CHAR pattern. Use BX constants when the column is BINARY / VARBINARY / BLOB.

Subsystem parameter LIKE_BLANK_INSIGNIFICANT changes how trailing blanks in CHAR/GRAPHIC columns are treated before LIKE. When it is enabled, trailing blanks in column data can be stripped before matching, while trailing blanks in the pattern remain significant. If your shop enables it, existing CHECK constraints that use LIKE may need CHECK DATA. Ask before you assume CHAR comparison matches VARCHAR the way a PC database would.

Performance notes

Predicate-processing tables in the performance manuals classify LIKE roughly like this (details depend on release and pattern):

• COL LIKE 'abc%' — can be matching, stage 1, indexable when the column is the leading index column and the prefix is a constant or host variable.
• COL LIKE '%abc' or '_abc' — stage 1 not indexable in the usual list (the leading wildcard or single-character wildcard stops a matching start-key).
• COL NOT LIKE 'char' — typically not indexable.
• COL LIKE '%' — matches every non-null string; useless as a filter and a hint you should omit the predicate.

Practical habits:

• Put the most selective constant prefix you can in the pattern.
• Do not write LIKE 'ABC%' when you meant COL = 'ABC' or a BETWEEN on a CHAR code.
• Avoid LIKE on expressions (SUBSTR, UPPER, concatenation of columns) if you need an index—consider a generated column or an index on the expression if the product level allows it.
• Leading-wildcard search on a huge table may need a dedicated search design (text extender, inverted index table, or a batch extract), not a casual %pattern%.

Explain It Like I'm Five

LIKE is matching stickers to a template. A blank square with a percent sign means “any bunch of letters, even none.” A single underscore square means “exactly one mystery letter.” If you need to find a sticker that actually has a percent sign drawn on it, you whisper a secret escape letter first so the percent is just ink, not a magic square. Searching for “starts with SAM” is like looking in the S drawer. Searching for “ends with SON” means opening every drawer. NOT LIKE is “does not match this template,” and a sticker with the name torn off (NULL) is not a yes.

Exercises

1. Write LIKE patterns for: starts with HA, ends with ER, contains LL, and five-character last names starting with T.
2. Write a LIKE that finds LASTNAME values containing a literal underscore, using ESCAPE '\' or '+'.
3. A COBOL PIC X(20) host variable holds 'HA%'. Explain why LIKE :HV may return no rows, and how to fix it.
4. Label each pattern as likely index matching or not: 'AB%', '%AB', 'A_B%', NOT LIKE 'A%'.
5. Rewrite UPPER(LASTNAME) LIKE 'SMITH%' as a design that could use an index (generated column, stored uppercase copy, or application-side uppercase insert).

Frequently asked questions

Is LIKE 'ABC' the same as = 'ABC'?

For a pattern with no wildcards, LIKE still follows LIKE matching and CHAR padding rules, which can differ from equality on trailing blanks. Prefer = when you mean exact equality. Use LIKE only when you need wildcards.

Can LIKE use a subquery as the pattern?

The pattern is an expression, not a fullselect. If the pattern comes from a table, join to that table or assign the pattern to a host variable. A scalar subquery that returns one string can be an expression in some contexts; keep it to one row or you will get a scalar subquery error.

Does LIKE work on CLOB?

Character large objects can participate in string predicates with restrictions and often without useful indexes. For large text search, LIKE '%term%' on a CLOB is a last resort. Prefer designed search tables or functions documented for LOBs.