LIKE Comparison

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 .

 

Table: LIKE comparisons

Comparison	Operation
begins with D	LIKE 'D%'
contains a D	LIKE '%D%'
has D in second position	LIKE '_D%'
begins with D and contains e	LIKE 'D%e%'
begins with D, contains e, then f	LIKE 'D%e%f%'
begins with non-D	NOT LIKE 'D%'

While the percent symbol (%) matches an unlimited number of characters, the underscore (_) matches only a single character. The underscore allows any single character to appear in that position. To test whether a field does not match a pattern, use NOT LIKE. To test for an actual percent symbol (%), use backslash-percent (\%). To test for an actual underscore (_), use backslash-underscore (\_).

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.