CODEWITHSUNDEEP
phpmysql
Matiur Rahman Mozumdar
Matiur Rahman Mozumdar

Reputation: 451

MySQL Wildcard Filtering Special Character using LIKE

My SQL query is:

SELECT * FROM listings  WHERE title LIKE '%Debbie\'s Pet Grooming%'

However, when I run this query I get no results. Can you please suggest why. My database table is below:

enter image description here

Upvotes: 6

Views: 821

Answers (2)

Nick
Nick

Reputation: 147166

Your problem is that somehow you have managed to get an actual backslash in the value in the database, so you need to search for that. Try this:

SELECT * FROM listings  WHERE title LIKE '%Debbie\\\\\'s Pet Grooming%'

Demo on dbfiddle

From the manual:

Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133370

You should use

WHERE title LIKE '%Debbie\\\'s Pet Grooming%' ;

. 

SELECT * FROM (
 select  "Debbie\'s Pet Grooming" title
)  t
WHERE title LIKE '%Debbie\\\'s Pet Grooming%' ;

but be user you have not space between Debbie and '

The first \ is for escape the second and the third for the single quote

Upvotes: 1

Related Questions