(mysql.info) string-comparison-functions

 
 12.3.1 String Comparison Functions
 ----------------------------------
 
 If a string function is given a binary string as an argument, the
 resulting string is also a binary string. A number converted to a
 string is treated as a binary string. This affects only comparisons.
 
 Normally, if any expression in a string comparison is case sensitive,
 the comparison is performed in case-sensitive fashion.
 
    * `EXPR LIKE PAT [ESCAPE 'ESCAPE_CHAR']'
 
      Pattern matching using SQL simple regular expression comparison.
      Returns `1' (`TRUE') or `0' (`FALSE'). If either EXPR or PAT is
      `NULL', the result is `NULL'.
 
      The pattern need not be a literal string. For example, it can be
      specified as a string expression or table column.
 
      Per the SQL standard, `LIKE' performs matching on a per-character
      basis, thus it can produce results different from the `='
      comparison operator:
 
           mysql> SELECT 'a"' LIKE 'ae' COLLATE latin1_german2_ci;
           +-----------------------------------------+
           | 'a"' LIKE 'ae' COLLATE latin1_german2_ci |
           +-----------------------------------------+
           |                                       0 |
           +-----------------------------------------+
           mysql> SELECT 'a"' = 'ae' COLLATE latin1_german2_ci;
           +--------------------------------------+
           | 'a"' = 'ae' COLLATE latin1_german2_ci |
           +--------------------------------------+
           |                                    1 |
           +--------------------------------------+
 
      With `LIKE' you can use the following two wildcard characters in
      the pattern:
 
      *Character*   *Description*
      `%'           Matches any number of characters, even zero characters
      `_'           Matches exactly one character
 
           mysql> SELECT 'David!' LIKE 'David_';
                   -> 1
           mysql> SELECT 'David!' LIKE '%D%v%';
                   -> 1
 
      To test for literal instances of a wildcard character, precede it
      by the escape character. If you do not specify the `ESCAPE'
      character, ‘`\'’ is assumed.
 
      *String*      *Description*
      `\%'          Matches one ‘`%'’ character
      `\_'          Matches one ‘`_'’ character
 
           mysql> SELECT 'David!' LIKE 'David\_';
                   -> 0
           mysql> SELECT 'David_' LIKE 'David\_';
                   -> 1
 
      To specify a different escape character, use the `ESCAPE' clause:
 
           mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
                   -> 1
 
      The escape sequence should be empty or one character long.  As of
      MySQL 5.0.16, if the `NO_BACKSLASH_ESCAPES' SQL mode is enabled,
      the sequence cannot be empty.
 
      The following two statements illustrate that string comparisons
      are not case sensitive unless one of the operands is a binary
      string:
 
           mysql> SELECT 'abc' LIKE 'ABC';
                   -> 1
           mysql> SELECT 'abc' LIKE BINARY 'ABC';
                   -> 0
 
      In MySQL, `LIKE' is allowed on numeric expressions. (This is an
      extension to the standard SQL `LIKE'.)
 
           mysql> SELECT 10 LIKE '1%';
                   -> 1
 
      * Because MySQL uses C escape syntax in strings (for example,
      ‘`\n'’ to represent a newline character), you must double any
      ‘`\'’ that you use in `LIKE' strings. For example, to search
      for ‘`\n'’, specify it as ‘`\\n'’. To search for
      ‘`\'’, specify it as ‘`\\\\'’; this is because the
      backslashes are stripped once by the parser and again when the
      pattern match is made, leaving a single backslash to be matched
      against.
 
    * `EXPR NOT LIKE PAT [ESCAPE 'ESCAPE_CHAR']'
 
      This is the same as `NOT (EXPR LIKE PAT [ESCAPE 'ESCAPE_CHAR'])'.
 
    * `EXPR NOT REGEXP PAT', `EXPR NOT RLIKE PAT'
 
      This is the same as `NOT (EXPR REGEXP PAT)'.
 
    * `EXPR REGEXP PAT' `EXPR RLIKE PAT'
 
      Performs a pattern match of a string expression EXPR against a
      pattern PAT. The pattern can be an extended regular expression.
      The syntax for regular expressions is discussed in  regexp.
      Returns `1' if EXPR matches PAT; otherwise it returns `0'. If
      either EXPR or PAT is `NULL', the result is `NULL'.  `RLIKE' is a
      synonym for `REGEXP', provided for `mSQL' compatibility.
 
      The pattern need not be a literal string. For example, it can be
      specified as a string expression or table column.
 
      * Because MySQL uses the C escape syntax in strings (for
      example, ‘`\n'’ to represent the newline character), you must
      double any ‘`\'’ that you use in your `REGEXP' strings.
 
      `REGEXP' is not case sensitive, except when used with binary
      strings.
 
           mysql> SELECT 'Monty!' REGEXP 'm%y%%';
                   -> 0
           mysql> SELECT 'Monty!' REGEXP '.*';
                   -> 1
           mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
                   -> 1
           mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
                   -> 1  0
           mysql> SELECT 'a' REGEXP '^[a-d]';
                   -> 1
 
      `REGEXP' and `RLIKE' use the current character set when deciding
      the type of a character. The default is `latin1' (cp1252 West
      European). *Warning*: These operators are not multi-byte safe.
 
    * `STRCMP(EXPR1,EXPR2)'
 
      `STRCMP()' returns `0' if the strings are the same, `-1' if the
      first argument is smaller than the second according to the current
      sort order, and `1' otherwise.
 
           mysql> SELECT STRCMP('text', 'text2');
                   -> -1
           mysql> SELECT STRCMP('text2', 'text');
                   -> 1
           mysql> SELECT STRCMP('text', 'text');
                   -> 0
 
      `STRCMP()' uses the current character set when performing
      comparisons. This makes the default comparison behavior case
      insensitive unless one or both of the operands are binary strings.
Info Catalog
(mysql.info) string-functions
automatically generated byinfo2html