Greater than and less than comparisons are possible
using the operators shown in Table .
Even more complex comparisons can be made. For instance, users often
need to compare character strings to see if they match a certain pattern.
Sometimes they want only fields that begin with a certain letter or
that contain a certain word. The LIKE keyword allows such
comparisons. The query in Figure returns
rows where the firstname begins with D.
test=> SELECT * FROM friend
test-> WHERE firstname LIKE 'D%'
test-> ORDER BY firstname;
firstname | lastname | city | state | age
Dean | Yeager | Plymouth | MA | 24
Dick | Gleason | Ocean City | NJ | 19
The percent symbol (%) means that any characters can follow the D. Thus the query performs the test firstname LIKE 'D%'.
The test firstname LIKE '%D%' returns those rows where firstname contains D anywhere in the field, not just at the beginning. The effect of having a % before and after a character is that the character can appear anywhere in the string.
More complex tests can be performed with LIKE, as shown in
Attempting to find all character fields that end with a certain character can be difficult. For CHAR() columns, like firstname, trailing spaces make trailing comparisons difficult with LIKE. Other character column types do not use trailing spaces. Those can, for example, use the test colname LIKE '%g' to find all rows that end with g. See Section for complete coverage of character data types.