Exact match searches are conducted by specifying literal values, as in:
SELECT * FROM table1 WHERE name = ’SMITH’;
The string 'SMITH' is a literal value.
Similar match searches are conducted by specifying an SQL wildcard that represents a character string that is similar to another character string. Logical expressions (such as those used in WHERE clauses and CHECK constraints) may use the "wildcard" characters and the keyword LIKE to match strings that are similar.
The underscore character ( _ ) is a wildcard character that matches any single character. For example, the following query:
SELECT * FROM table1 WHERE first_name LIKE ’J_NE’;
returns both JANE and JUNE (as well as any other four-character name where the first letter is J and the last two letters are NE).
The percent character ( % ) is a wildcard character that matches any occurrence of 0 or more characters. For example, the following query:
SELECT * FROM table1 WHERE first_name LIKE ’JOHN%’;
could return JOHN, JOHNNY, JOHNATHAN, and so on.
The % wildcard is used most often at the end of strings, but it can be used anywhere. For example, the following search pattern:
LIKE ’%JO%’
returns all people who have JO somewhere in their name, included but not limited to:
JOANNE, BILLY JO, and LONG JOHN SILVER
Multiple wildcards are allowed in a single string. For example, the string J_V_ matches JAVA and JIVE and any other four-character words or names that startwith J and have V as the third character. Note that because the underscore (_) only matches exactly one character, the string J_V_ does not match the string JOVIAL, which has more than four characters.