CODEWITHSUNDEEP
mysqlescapingsql-like
urig
urig

Reputation: 16831

MySQL: Why is does escaping not working in the LIKE operator?

I'm experimenting with pattern matching strings in MySQL 8.0:

CREATE TABLE foo (bar VARCHAR(250));

INSERT INTO `foo` (`bar`) VALUES ('\%');

As an exercise, I'd like to write a specific query using LIKE that returns rows that are "like the literal \%". (I know this is should actually be done with = comparison but this is an exercise)

  1. As % is a wildcard character, I cannot simply use:

    SELECT * from `foo` WHERE `bar` LIKE '\%';

  2. When I try to escape using the default escape character \ it also fails:

    SELECT * from `foo` WHERE `bar` LIKE '\\%';

  3. And likewise when I tryin to use the ESCAPE keyword to define a non-default escape character:

    SELECT * from `foo` WHERE `bar` LIKE '\|%' ESCAPE '|';

Why do queries 2 and 3 not work for me?

Upvotes: 1

Views: 755

Answers (1)

nbk
nbk

Reputation: 49373

three is simple.

% is not a wildcard is t simply a character like every character else

SELECT * from `foo` WHERE `bar` LIKE '\%' ESCAPE '|';

Works just fine

The second works too.

But you need to

SELECT * from `foo` WHERE `bar` LIKE '\\\%';

I remember You have to escape the escape and the wildcard!

Upvotes: 1

Related Questions