POLARDB compatible with Oracle provides pattern matching by using the traditional SQL LIKE operator. The syntax of the LIKE operator is as follows.

string LIKE pattern [ ESCAPE escape-character ]
string NOT LIKE pattern [ ESCAPE escape-character ]

Each pattern parameter defines a set of strings. The LIKE expression returns true if the set of strings represented by pattern contains the value specified by the string parameter. As expected, a reciprocal inverse relationship exists and the NOT LIKE expression returns FALSE if LIKE returns TRUE. An equivalent expression of NOT LIKE is NOT (string LIKE pattern).

If the pattern does not contain percent signs (%) or underscores (_), the pattern only represents the string itself. In this case, the LIKE operator acts like the equals operator. An underscore (_) in pattern matches any single character. A percent sign (%) matches any string of zero or more characters.


'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false    

LIKE pattern matches cover the entire string. If you want to start matching a pattern from any position in the string, the pattern must start and end with a percent sign.

If you want to match a literal underscore or percent sign without matching other characters, you must precede the respective character in the pattern by an escape character. The default escape character is a backslash (\). However, you can also use the ESCAPE clause to specify a different escape character. To match the escape character itself, write two escape characters.

Note that the backslash already has a specific meaning in string literals. To write a pattern that contains a backslash, you must write two backslashes in an SQL statement. Therefore, writing a pattern that matches a literal backslash means writing four backslashes in the statement. You can avoid this by using the ESCAPE clause to specify a different escape character. Then, a backslash does not provide a special meaning to LIKE anymore. (However, the backslash still has a special meaning for the string literal parser, and two backslashes are still required.)

You can also select no escape character by writing ESCAPE ''. This effectively disables the escape mechanism, which makes it impossible to disable the special meaning of underscores and percent signs in the pattern.