How to escape the percent character (%) in LIKE?

Question

How to escape the percent character (%) in LIKE?

Navigation

#1 by (5 votes)
#2 by (3 votes)

5

I have a system where I use Laravel to query the database. This is a search form.

I use the following code:

$search = trim(Input::get('search'));

return Empresa::where('nome_fantasia', 'LIKE', $search)->get();

The problem is that, since I use LIKE to query by name, if the user adds in the % (percent sign) search, it returns all the data since it is a character special of % .

How do I escape the percentage character in MYSQL?

Note : I've just added the tag php and mysql . The Laravel in this case is just the tool I'm using, but the solution can serve indepently of whether I use it or not.

php mysql

asked by anonymous 16.02.2016 / 19:53

2 answers

3

Just use the bracketed character eg:

Search value 75%

WHERE MinhaColuna LIKE '75[%]'

It works on the vast majority of DBMSs available on the market.

16.02.2016 / 19:56

Delete folders in Github Detect if a given page is opened within an IFRAME

score 5 · Accepted Answer

You need to escape it:

From the tests I did on SQLFiddle . By default the \ is already the ESCAPE , such that just doing:

SELECT * FROM tb_table
WHERE nome_fantasia LIKE 'name\%name';

Already solves the problem, however if you want to use another character like ESCAPE simply declare it:

SELECT * FROM tb_table
WHERE nome_fantasia LIKE 'name|%name' ESCAPE '|';

If you want to escape through PHP, you can also use the addcslashes function to escape only the % character.

Do this:

$search = '% meu texto aqui';

$search = addcslashes($search, '%');

This will return:

 '\% meu texto aqui'