Introduction to Databases for the Web: Pt. 2 - Page 4

Like

The LIKE operator is another extremely powerful tool to help you define your search. In its most simple form, the LIKE operator functions just like the "=" operator. That is, it matches for equality in the WHERE clause. Thus, if we were to search our EMPLOYEES table, the statement:

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE 
   EMP_NAME = 'Lim Sing Yuen';

Would be the same as saying:

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE 
   EMP_NAME LIKE 'Lim Sing Yuen';

In either case, you would get:

EMP_NAME      EMP_NUM
---------------------
Lim Sing Yuen     002
---------------------

However, the real umph of the LIKE operator comes from its ability to incorporate wildcards. Specifically, the LIKE operator can be used with the "%" and the "_" operator. The "%" operator can be used to match any string of any length and the "_" operator is used to match any single character. But this is best seen by example. Consider the following:

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE EMP_NAME LIKE 'Lim%';

In this case, you would get the following:

EMP_NAME       EMP_NUM
----------------------
Lim Li Chuen       001
Lim Sing Yuen      002
----------------------

Notice that in both cases, the search string begins with "Lim". The "%" wildcard specifies that so long as the string begins with the string "Lim", it matches.

Consider the "_" operator.

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE EMP_NUM LIKE '00_';

In this case, you would get all the records returned that included a "00" followed by any other character:

EMP_NAME       EMP_NUM
----------------------
Lim Li Chuen       001
Lim Sing Yuen      002
Loo Soon Keat      003
----------------------

Null

A final WHERE clause operator is the IS NULL/IS NOT NULL operator. This operator is used to test whether or not a column has a value or not. For example, we can grab all the columns from the EMPLOYEES table that have employee numbers using the following command.

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE EMP_NUM IS NOT NULL;

In response, you would get:

EMP_NAME       EMP_NUM
----------------------
Lim Li Chuen       001
Lim Sing Yuen      002
Loo Soon Keat      003
----------------------