Skip to main content

LIKE Operators

Sometimes, for columns holding characters, you need to search for a value that partly matches a string of characters. For example, find all students whose names start with 'Richard'. This could include Richard, Richards, Richardson, Richard-Smythe, etc.

This search can be achieved using the wildcard character % and the LIKE clause.

For example:

SELECT forename, surname FROM student WHERE surname LIKE 'Richard%';

The wildcard character, %, corresponds to any number of characters.

The wildcard character can also be placed at the beginning of the character string. For example, find all students whose names end in 'son'. This could include Richardson, Johnson, Peterson, etc. The SQL SELECT command could be:

SELECT forename, surname

FROM student

WHERE surname LIKE '%son';

Furthermore, a combination of both can be used. For example, find all students whose surnames start with, end with or contain a specific string of characters. However, there is a complication associated with this, which will be discussed later in this outcome (see UPPER and LOWER function).

One final point: all strings are case sensitive. For example, when searching for 'Richard%', if a surname is stored as Richardson it will be found, whereas, RICHARDSON will not be found. Again, a solution to this is discussed later in this outcome.

Next: IS NULL (IS NOT NULL) Operators