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) can use the "wildcard" characters and the keyword LIKE to match strings that are similar.
Wildcard characters
The underscore character ( _ ) matches any single character. For example, this 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 ( % ) matches any occurrence of 0 or more characters. For example, this 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, this search pattern:
LIKE '%JO%'
returns all people who have JO somewhere in their name, including, but not limited to:
JOANNE, BILLY JO, and LONG JOHN SILVER
Multiple wildcards are allowed in a single string. For example, J_V_ matches JAVA and JIVE and any other four-character words or names that start with J and have V as the third character. Because _ matches only exactly one character, J_V_ does not match JOVIAL, which has more than four characters.