CODEWITHSUNDEEP
mysqlwildcardwhere-clause
Riz
Riz

Reputation: 10256

mysql wildcards % vs %%

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

Answers (1)

Filip Roséen
Filip Roséen

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.

MySQL documentation of LIKE

What's the origin of the string, and where is it going?

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%'";

A few sample SELECTs using the LIKE operator

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

Related Questions