Полезная информация

next up previous contents index
Next: Regular Expressions Up: Customizing Queries Previous: AND/OR usage

LIKE Comparison

Greater than and less than comparisons are possible, as shown in table [*], and there are even more complex comparisons available. Users often need to compare character strings to see if they match a certain pattern. For example, sometimes you only want fields that begin with a certain letter, or contain a certain word. The LIKE keyword allows such comparisons. The query in figure [*] returns rows where the firstname begins with D.

  
Figure: Firstname begins with D.
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...vert Ocean~City~~~~~\vert NJ~~~\vert~19
\par (2~rows)\end{list}\par
\end{figure}

The percent (%) is interpreted to mean any characters can follow the D. The query performs the test firstname LIKE 'D%'.

The test firstname LIKE '%D%' returns rows where firstname contains a D anywhere in the firstname field, not just at the beginning. The effect of the having a % before and after a character allows the character to appear anywhere in the string.

More complex tests can be performed with LIKE, as shown in figure [*].

 
Table: LIKE comparison
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 percent (%) matches an unlimited number of characters, the underscore (_) matches only a single character. The underscore allows any single character to appear in its position. To test if a field does not match a pattern, use NOT LIKE. To test for an actual percent sign (%), use %%. An actual underscore (_) is tested with two underscores.

Attempting to find all character fields that end with a certain character can be difficult. For certain column types, it is quite easy. Just doing colname LIKE '%g' is enough to find all rows that end with g. For char() columns, like firstname, there are trailing spaces that make such comparisons difficult with LIKE. See section [*] for complete coverage on character data types.


next up previous contents index
Next: Regular Expressions Up: Customizing Queries Previous: AND/OR usage
Bruce Momjian
1999-11-21