Module 5: DB2 SQL SELECT and Filtering

LIKE

LIKE does pattern matching on character data - names starting with S, phone numbers ending in 38. It is the tool for 'I know part of the value' searches.

The two wildcards

% matches any sequence of characters, including none: 'S%' finds STERN and S.
_ (underscore) matches exactly one character: 'SM_TH' finds SMITH.
Patterns without wildcards behave like = : WHERE NAME LIKE 'HAAS'.
Wildcards can combine: '%SON%' finds anything containing SON.

LIKE is for character data

LIKE applies to CHAR, VARCHAR and GRAPHIC columns.
Do not use LIKE on numbers or dates - convert or use range comparisons instead.
Matching is case-sensitive: 's%' will not find 'STERN'.
Trailing blanks in CHAR columns: 'HAAS%' matches 'HAAS ' fine because % covers the blanks.

The ESCAPE clause

To search for a literal % or _, declare an escape character.
Example: WHERE COL LIKE '50!% off' ESCAPE '!' finds the literal text '50% off'.
Without ESCAPE there is no way to match a real percent sign.
Pick an escape character that never appears in your data, like '!' or '\\'.

Example

SELECT LASTNAME, FIRSTNME, PHONENO FROM EMP WHERE LASTNAME LIKE 'S%'; -- % matches any ending: STERN matches, HAAS does not LASTNAME FIRSTNME PHONENO -------- -------- ------- STERN IRVING 6423

Backward