Network Computingwww.networkcomputing.com

Using LIKE

So far we have learned how to find exact matches with SQL. However, there are likely to be many times that we may need to search for partial strings. Maybe we want to find everyone that has a first name starting with C or a book with "computer" in the title. SQL provides a LIKE operator for just this type of query.

The LIKE operator can only be used on fields that have one of the string types set as their data type. LIKE cannot be used on dates or numbers.

LIKE is used when searching for inexact string matches in a table. It is used within the WHERE clause, and uses the following format:

WHERE
field LIKE expression

The field can be any field in a table, and the expression is the type of comparison being used. The most common comparison operator is the percent symbol (%), or the asterisk (*) in Access. This is the wildcard character used for pattern matching within the LIKE operator. For example, we may want to find everyone with a first name that begins with C, so we would use the wildcard operator in the following way:

WHERE
FirstName LIKE 'C%'

This will return everyone with a name starting with C. We can also use the wildcard at the beginning of a clause. If we were looking for a street address that includes 'Ave.', we could write our WHERE clause as follows:

WHERE
Address LIKE '%Ave.'

We can go one step further and look for substrings in a field. If we have a jobs table that has a JobDescription field, then we can use the wildcard to search for the string 'SQL' anywhere in the text:

WHERE
JobDescription LIKE '%SQL%'

The LIKE operator will prove to be very useful as we write more complex SQL statements, since it enables us to find partial matches without performing any complicated string manipulation. We should keep in mind that the LIKE operator is not the most efficient SQL command, and will degrade overall performance. If we know the exact string that we are looking for in a field, then we should use the = operator instead of LIKE. Adding an index on a field that is often searched using the LIKE operator may increase the system performance.

In addition to the % wildcard, there are two other important wildcards used with the LIKE operator:

• the underscore (_)
• square brackets ([])

We can combine the operators to further refine searches. For instance, if we were looking for last names that started in a similar way to Smith, but don't care about the length, then we could use both the _ and the % to find the appropriate records:

WHERE
LastName LIKE '_mith%'

This would return names such as Smith and Smithfield.

Additionally, we can use the brackets ([]) to further limit ranges of characters. With the brackets, we can specify particular characters that must appear in a particular position. For instance, if we were looking for words like night, light, and fight, but we only want to return night and light, then we need to modify our criteria since:

WHERE
Word LIKE '_ight'

will return 'fight' as well as 'night' and 'light'. We want to limit our search to words that start only with n or l, so we specify this by putting these characters within brackets, in the appropriate place:

WHERE
Word LIKE '[nl]ight'

This search criteria will only look for words that end in 'ight' and begin with an n or an l. Keep in mind that the brackets may only contain single characters, so we cannot use them for lists of substrings. This is the biggest limitation to the bracket wildcard, but there are still a large number of possibilities for expression searching in strings.