PolarDB allows you to use the traditional SQL LIKE operator to implement pattern matching.


The following example shows the syntax of the LIKE operator:

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

Each pattern parameter defines a set of strings. If the set of strings represented by pattern contains the value that is specified by the string parameter, the LIKE expression returns true. A NOT LIKE expression has a reciprocal inverse relationship with a LIKE expression that contains the same string and pattern parameters. If the LIKE expression returns true, the NOT LIKE expression returns false. The NOT LIKE expression is equivalent to NOT (string LIKE pattern).

If the pattern parameter does not contain percent signs (%) or underscores (_), the specified pattern represents only the string itself. In this case, the LIKE operator acts as an equal sign operator. An underscore (_) in the pattern parameter matches a single arbitrary character. A percent sign (%) matches an arbitrary 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 match a pattern from an arbitrary position in the string, the pattern must start and end with a percent sign.
'abc'     LIKE   '%b%'    true
If you want to match only a literal underscore or a percent sign, you must precede the target 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.
'abc_d'     LIKE   '%\_%'    true
'abc%d'     LIKE   '%\%%'    true
'abc_d'     LIKE   '%/_%'    ESCAPE  '/'  true
'abc\d'     LIKE   '%\\%'    true
Note that the backslash already has a special meaning in string literals. To write a pattern that contains a backslash, you must write two backslashes in an SQL statement. Therefore, to write a pattern that matches a literal backslash, you must write four backslashes in the statement. You can avoid this by using the ESCAPE clause to specify a different escape character. Then, a backslash no longer provides a special meaning to LIKE. However, you still must write two backslashes because the backslash still has a special meaning for the string literal parser.
'ab\c'     LIKE  '%\\%'    true
'ab\\c'    LIKE  '%\\\\%'  true
'ab\\c'    LIKE  '%\\%'    ESCAPE  '/'  true
You can also select no escape character by writing ESCAPE ''. This disables the escape mechanism and ensures that the special meaning of underscores and percent signs are enabled in patterns.
'abc%d'    LIKE  '%\%%'  true
'abc%d'    LIKE  '%\%%'  ESCAPE  ''  false