6.3.2.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, pass it as the argument to CONCAT().

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 only affects 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). With LIKE you can use the following two wildcard characters in the pattern:

Char	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 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 SQL-99 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). Note: Currently LIKE is not multi-byte character safe. Comparison is done character by character.

expr NOT LIKE pat [ESCAPE 'escape-char']

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

expr SOUNDS LIKE expr

Same as SOUNDEX(expr)=SOUNDEX(expr) (available only in version 4.1 or later).

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. See section G MySQL Regular Expressions. 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 (col1,col2,...) AGAINST (expr IN BOOLEAN MODE)

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. MATCH ... AGAINST() is available in MySQL version 3.23.23 or later. IN BOOLEAN MODE extension was added in version 4.0.1. For details and usage examples, see section 6.8 MySQL Full-text Search.

User Comments

Keep in mind that

LIKE '%'

Does not match NULLs!

Also, keep in mind that by default,
Swedish/Finnish string comparison rules are in
use! This means that e.g.: u <> ü = y !

mysqladmin --version
Ver 8.37 Distrib 4.0.3-beta, for pc-linux-gnu on i686

STRCMP doesn't work prefectly on my system
mysql> select STRCMP("beeplove", "beeplovE");
+--------------------------------+
| STRCMP("beeplove", "beeplovE") |
+--------------------------------+
| 0 |
+--------------------------------+
1 row in set (0.00 sec)

Mohammad's example shows how STRCMP() works (string comparison is NOT case sensitive by default, but only when one of the expressions to be compared are case sensitive):
The following one IS case sensitive:
select STRCMP('beehive', BINARY 'BeEhiVE') /* returns 1 */;

To match arabic strings (eg: search for arabic words in arabic fields) we should consider using BINARY for example:
SELECT * FROM table WHERE BINARY field LIKE '%ÚÑÈí%'

For more tips, examples about using MySQL with Arabic data visit: http://www.php4web.com/article.php?sid=105&mode=thread&order=0

What I'm really missing in MySQL is some function to calculate the Levenshtein distance between two strings, or something similar to the PHP similar_text() function, which would be an invaluable tool.

Add your own comment.