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
(2 rows)
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 Table .
|
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.