Table 11.7. String Comparison Operators
Name | Description |
---|---|
LIKE |
Simple pattern matching |
NOT LIKE |
Negation of simple pattern matching |
STRCMP() |
Compare two strings |
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 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+ | 'ä' LIKE 'ae' COLLATE latin1_german2_ci | +-----------------------------------------+ | 0 | +-----------------------------------------+ mysql>SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+ | 'ä' = 'ae' COLLATE latin1_german2_ci | +--------------------------------------+ | 1 | +--------------------------------------+
In particular, trailing spaces are significant, which is not
true for CHAR
or
VARCHAR
comparisons performed
with the =
operator:
mysql> SELECT 'a' = 'a ', 'a' LIKE 'a ';
+------------+---------------+
| 'a' = 'a ' | 'a' LIKE 'a ' |
+------------+---------------+
| 1 | 0 |
+------------+---------------+
1 row in set (0.00 sec)
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 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.
Exception: At the end of the pattern string, backslash can
be specified as “\\
”. At
the end of the string, backslash stands for itself because
there is nothing following to escape. Suppose that a table
contains the following values:
mysql> SELECT filename FROM t1;
+--------------+
| filename |
+--------------+
| C: |
| C:\ |
| C:\Programs |
| C:\Programs\ |
+--------------+
To test for values that end with backslash, you can match the values using either of the following patterns:
mysql>SELECT filename, filename LIKE '%\\' FROM t1;
+--------------+---------------------+ | filename | filename LIKE '%\\' | +--------------+---------------------+ | C: | 0 | | C:\ | 1 | | C:\Programs | 0 | | C:\Programs\ | 1 | +--------------+---------------------+ mysql>SELECT filename, filename LIKE '%\\\\' FROM t1;
+--------------+-----------------------+ | filename | filename LIKE '%\\\\' | +--------------+-----------------------+ | C: | 0 | | C:\ | 1 | | C:\Programs | 0 | | C:\Programs\ | 1 | +--------------+-----------------------+
expr
NOT LIKE pat
[ESCAPE
'escape_char
']
This is the same as NOT
(
.
expr
LIKE
pat
[ESCAPE
'escape_char
'])
Aggregate queries involving NOT
LIKE
comparisons with columns containing
NULL
may yield unexpected results. For
example, consider the following table and data:
CREATE TABLE foo (bar VARCHAR(10)); INSERT INTO foo VALUES (NULL), (NULL);
The query SELECT COUNT(*) FROM foo WHERE bar LIKE
'%baz%';
returns 0
. You might
assume that SELECT COUNT(*) FROM foo WHERE bar
NOT LIKE '%baz%';
would return
2
. However, this is not the case: The
second query returns 0
. This is because
NULL NOT LIKE
always returns
expr
NULL
, regardless of the value of
expr
. The same is true for
aggregate queries involving NULL
and
comparisons using
NOT
RLIKE
or NOT
REGEXP
. In such cases, you must test explicitly
for NOT NULL
using
OR
(and not
AND
), as shown here:
SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%' OR bar IS NULL;
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.
User Comments
Also, keep in mind that by default,
Swedish/Finnish string comparison rules are in
use! This means that e.g.: u <> ü = y !
In the manual it states "Before MySQL 4.0, STRCMP() is case sensitive.", I did not relize this, and I found out the hard way that STRCMP() is case-sensitive.
Also I found it strange that STRCMP() is case-sensitive, but ORDER BY is case-insensitive.
Here is the what I was doing, and I hope this helps others. I have a table that I display, just a basic dump of the table order by a varchar field. I have a section to display a single item from the list, then used the STRCMP to go to the next/prevoius item in the order it was displayed
Listing the table:
SELECT id, name, price, description WHERE category = 1 ORDER BY name
Getting the Next Item Button for display section:
SELECT id WHERE category = 1 AND STRCMP(name, 'current_item_name') = 1 ORDER BY name LIMIT 1
Getting the Prev Item Button for display section:
SELECT id WHERE category = 1 AND STRCMP(name, 'current_item_name') = -1 ORDER BY name DESC LIMIT 1
The Fix I use for now, is to lowercase the strings in STRCMP():
STRCMP(LOWER(name), LOWER('current_item_name'))
It is possible to search wildchar patterns when using bind parameters.
For example to search a substring:
select * from tblUser where Name LIKE CONCAT('%', ? ,'%');
And then (for example in perl DBI)
$dbh->selectrow_hashref($SQL_Stmnt, undef, @{[ 'Erik']});
This provides a 'safer' search environment in web interfaces, since it prevents SQL injections.
Add your own comment.