SQL: LIKE condition

Like is another keyword that is used in the Where clause. It is used to match string pattern values. The Like condition allows you to use wildcard characters to specify one or more string character values. ANSI/SO SQL defines two wildcard characters, the underscore (_) and the percent (%). These are the characters that are almost universally used in commercial SQL systems for pattern matching. String pattern matching is useful in cases where you are not sure of the exact string value that you need to search for. Basically, Like allows you to do a search based on a pattern rather than specifying exactly what is desired (as in IN) or spell out a range (as in Between).

The syntax for Like is as follows:

SELECT column_name FROM derived_table WHERE column_name [NOT] LIKE {PATTERN}

Example #1

SELECT FirstName, LastName FROM EmployeeAddressTable WHERE FirstName LIKE 'S%'

The percent sign (%) is used to represent any possible character (number, letter, or punctuation) or set of characters that might appear after the 'S'. In this query we are going to find all of the employees whose first name begins with 'S'.

Example #2

SELECT * FROM EmployeeAddressTable WHERE State NOT LIKE 'J%'

By using NOT, you can select everything that does not match a specific string. The addition of NOT in this example creates a list of all employees whose first name does not start with 'J'.

Example #3

SELECT * FROM EmployeeAddressTable WHERE FirstName LIKE 'Sa_'

This SQL statement would return all employees whose first name is 3 characters long, where the first two characters is 'Sa'.

Example #4

SELECT * FROM EmployeeAddressTable WHERE LastName LIKE '%c%'

The use of wild card characters, in the form of a regular expression, can be a very complex subject. Fortunately, there are several simple ways to use wild cards that are very useful for pattern matching. By placing a % before and after the character 'c' (i.e., '%c%' ) you can search for all employees whose last name contains the characters 'c'. The % will accept zero or more characters in the lastname before or after the character 'c'.