(mysql.info.gz) String comparison functions

Info Catalog

(mysql.info.gz) String functions

(mysql.info.gz) String functions

 
 12.3.1 String Comparison Functions
 ----------------------------------
 
 MySQL automatically converts numbers to strings as necessary, and vice
 versa.
 
      mysql> SELECT 1+'1';
              -> 2
      mysql> SELECT CONCAT(2,' test');
              -> '2 test'
 
 If you want to convert a number to a string explicitly, use the `CAST()'
 or `CONCAT()' function:
 
      mysql> SELECT 38.8, CAST(38.8 AS CHAR);
              -> 38.8, '38.8'
      mysql> SELECT 38.8, CONCAT(38.8);
              -> 38.8, '38.8'
 
 `CAST()' is preferable, but it is unavailable before MySQL 4.0.2.
 
 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.
 
      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 the
      character with the escape character.  If you don't 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 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
 
      Note: Because MySQL uses the C escape syntax in strings (for
      example, `\n' to represent newline), 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']'
      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 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.
 
      Note: Because MySQL uses the C escape syntax in strings (for
      example, `\n' to represent newline), you must double any `\' that
      you use in your `REGEXP' strings.
 
      As of MySQL 3.23.4, `REGEXP' is not case sensitive 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.
      However, 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
 
      As of MySQL 4.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.  Before MySQL 4.0, `STRCMP()' is case sensitive.
 
 

Info Catalog

(mysql.info.gz) String functions

(mysql.info.gz) String functions