Reputation: 10256
What is the difference in '%' and '%%', when used in mysql where clause with 'LIKE' ?
select * from `wp_users` u where u.user_nicename like "%lastuser%"
VS
select * from `wp_users` u where u.user_nicename like "%%lastuser%%"
Upvotes: 6
Views: 11577
Reputation: 63902
There is no difference between %%
and %
when it comes to pattern matching in mysql.
I've seen developers get confused over this when they try to match a literal %
and therefor write %%
. This is most often because of the fact that format-strings often use a double %
to indicate that you'd like it to be treated as an exact literal.
LIKE
If the string is passed to a function such as sprintf
the format-string rule I mentioned earlier is present, though there is no confusion in that case.
The developer want it to be a single %
in the string passed to mysql, and therefor wrote %%
.
$query = sprintf (
"SELECT ... FROM ... WHERE id <> %d AND data LIKE '%%hello world%%'",
50
);
// $query => "SELECT ... FROM ... WHERE id <> 50 AND data LIKE '%hello world%'";
mysql> SELECT 'abc' LIKE 'ab%';
+------------------+
| 'abc' LIKE 'ab%' |
+------------------+
| 1 |
+------------------+
1 row in set (0.01 sec)
mysql> SELECT 'abc' LIKE 'ab%%';
+-------------------+
| 'abc' LIKE 'ab%%' |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT 'abc' LIKE 'ab\%';
+-------------------+
| 'abc' LIKE 'ab\%' |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT 'ab%' LIKE 'ab\%';
+-------------------+
| 'ab%' LIKE 'ab\%' |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.00 sec)
Upvotes: 10