How to search through database?

A simple way of creating searching queries is by using the LIKE operators. In broader terminology, the LIKE operators are used to query data against given or selected patterns. The following example shows how to use a LIKE operator in a simple way:


SELECT employee, lastName, firstName FROM employees

WHERE firstName LIKE 'a%';

As we can see above, this operator is usually used with the SELECT WHERE statement. What we can also see is that it used a wildcard %. The wildcard percentage (%) represents a match of any string, zero or more characters. The other wildcard underscore (_) may be used as a match of any single character.

In retrospective, the example above will search through database looking for all employees that have the first name starting with 'a'.

In the following example, the underscore (_) wildcard will be used to match all employees that are in their twenties:

SELECT employee, age FROM employees WHERE age LIKE '2_';

Sometimes we want to retrieve all data but some, and in such a case, the operator NOT comes in handy. Let's look in this example:

SELECT employee, state FROM employees WHERE age NOT LIKE 'k_';

This example will extract all employees coming from U.S. states that do not start with 'k', such as Kentucky or Kansas.

Escaping wildcard characters in a search pattern with the ESCAPE clause

By using the ESCAPE clause we can 'assist' our search pattern to avoid reading the wildcard characters like they are, but rather understating them as regular ones. Let's look into this example:

SELECT score FROM score_sheet WHERE score LIKE '20%$' ESCAPE '$';

In this example the ESCAPE clause tells the MySQL that the character $ is used to escape the percentage (%) and read it as a literal %. However the wildcard character for escape is backslash (\), and therefore the example above may be written also like this:

SELECT score FROM score_sheet WHERE score LIKE '20%\';