GNU Info

(mysql.info)String comparison functions

---

Next: Case Sensitivity Operators Prev: String functions Up: String functions

---

Enter node , (file) or (file)node

String Comparison Functions
...........................

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).  With `LIKE' you can use the
     following two wild-card characters in the pattern:

     `%'     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 wild-card character, precede
     the character with the escape character.  If you don't specify the
     `ESCAPE' character, `\' is assumed:

     `\%'    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 following two statements illustrate that string comparisons are
     case insensitive unless one of the operands is a binary string:

          mysql> select 'abc' LIKE 'ABC';
                  -> 1
          mysql> SELECT 'abc' LIKE BINARY 'ABC';
                  -> 0

     `LIKE' is allowed on numeric expressions! (This is a MySQL
     extension to the ANSI SQL `LIKE'.)

          mysql> select 10 LIKE '1%';
                  -> 1

     Note: Because MySQL uses the C escape syntax in strings (for
     example, `\n'), you must double any `\' that you use in your `LIKE'
     strings.  For example, to search for `\n', specify it as `\\n'.  To
     search for `\', specify it as `\\\\' (the backslashes are stripped
     once by the parser and another time when the pattern match is
     done, leaving a single backslash to be matched).

`expr NOT LIKE pat [ESCAPE 'escape-char']'
     Same as `NOT (expr LIKE pat [ESCAPE 'escape-char'])'.

`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.
     Note: Regexp.  Returns `1' if `expr' matches `pat', otherwise
     returns `0'.  `RLIKE' is a synonym for `REGEXP', provided for
     `mSQL' compatibility. Note: Because MySQL uses the C escape syntax
     in strings (for example, `\n'), you must double any `\' that you
     use in your `REGEXP' strings.  As of MySQL Version 3.23.4,
     `REGEXP' is case insensitive for normal (not 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 (ISO-8859-1
     Latin1 by default) when deciding the type of a character.

`expr NOT REGEXP pat'
`expr NOT RLIKE pat'
     Same as `NOT (expr REGEXP pat)'.

`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

`MATCH (col1,col2,...) AGAINST (expr)'
     `MATCH ... AGAINST()' is used for full-text search and returns
     relevance - similarity measure between the text in columns
     `(col1,col2,...)' and the query `expr'. Relevance is a positive
     floating-point number. Zero relevance means no similarity.  For
     `MATCH ... AGAINST()' to work, a *FULLTEXT* index must be created
     first. Note: `CREATE TABLE'.  `MATCH ... AGAINST()'
     is available in MySQL Version 3.23.23 or later. For details and
     usage examples Note: Fulltext Search.