(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
automatically generated byinfo2html