Match '%' sign when searching in MySQL database
I would like to match this 'wildcard %'
in MySQL.
\%
and it is not working.The default escape character is \
. So just prefix %
with a \
as: \%
:
The manual clearly says:
To test for literal instances of a wild-card character, precede it by the escape character. If you do not specify the ESCAPE character, “\” is assumed.
Search for %
in Stack%Overflow
:
mysql> select 'Stack%Overflow' like '%\%%';
+------------------------------+
| 'Stack%Overflow' like '%\%%' |
+------------------------------+
| 1 | <----- Found
+------------------------------+
1 row in set (0.00 sec)
Search for %
in StackOverflow
:
mysql> select 'StackOverflow' like '%\%%';
+-----------------------------+
| 'StackOverflow' like '%\%%' |
+-----------------------------+
| 0 | <----- Not Found
+-----------------------------+
1 row in set (0.00 sec)
EDIT:
If you are calling this query from PHP, you'll have to use \\
. This is because even PHP uses \
as the escape character. So make MySQL get a \
you need to have \\
in PHP.
ok i need to use doble (\\)
to match the % in the database
Here is an example:
$sql = 'SELECT * FROM tableName WHERE fieldName LIKE "wildcard\%"';
You need to escape the \ in PHP string as well, otherwise PHP would think that you are actually escaping the %, thus sending literal % to the sql query, so I think this should work:
mysql_query("select * from bla where bli like '\\%somewords\\%'");
In latest version you can also try
select * from tbl1 where TxtExpr REGEXP '^%';
regards
精彩评论