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

Asked

Viewed 1,259 times

5

I have a system where I use the Laravel to make the query in the database. It 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, how do I use the LIKE to query by name, if the user adds to the query % (percentage sign), it brings all the data, since it is a special character of the %.

How do I escape the percentage character in MYSQL?

Note: I just added the tag php and mysql. The Laravel in this case it is only the tool I am using, but the solution can serve indelibly from I use it or not.

php mysql

  • What is wrong with my question? What can be improved?

  • http://answall.com/q/87474/91 :D

  • @rray I had already asked and forgot! kkkkk. Duplicated, Ué.

  • I can’t say ... the context seems different.

  • The escape functions of both PDO and mysqli already escape this type of character. I don’t know how the Laravel works with it.

2 answers

5


You need to escape it:

For the tests I did on Sqlfiddle. By default the \ already is the ESCAPE, in such a way that just doing:

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

It already solves the problem, however if you want to use another character as ESCAPE just 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 function addcslashes to escape only the character %.

Do so:

$search = '% meu texto aqui';

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

This will return:

 '\% meu texto aqui'

4

Just use the character between brackets ex:

Value sought 75%

WHERE MinhaColuna LIKE '75[%]'

Works on the vast majority of`s DBMS available on the market.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.