CODEWITHSUNDEEP
mysqlsql
Jonathan
Jonathan

Reputation: 43

Issue when escaping '%' character

I have an image table with a name column. The value inside the name column may contain a percentage sign, which by itself is a wild card character when using the like operator. I currently have the following relevant values in the name column: "a%" and "blah%blah".

I do not get any results, as expected, when performing: 

select name from image where name like '\%'

When performing the following, I am getting 2 of the above mentioned records:

select name from image where name like '%\%'

I understand why I am getting the record with "a%" as the name. However, I do not understand why I am getting "blah%blah". If "blah%blah" had a percentage character as the last character, it would make sense, but it does not.

Am I doing something wrong here or is this a MySQL bug?

Upvotes: 3

Views: 48

Answers (1)

cha
cha

Reputation: 10411

As described in this bug, you need to use another collation as a workaround:

SQL Fiddle

Using utf8_unicode_ci colation:

CREATE TABLE image
    (`id` int, `name` varchar(55) collate utf8_unicode_ci)
;

INSERT INTO image
    (`id`, `name`)
VALUES
    (1, 'a%'),
    (2, 'blah%blah')
;

Query 1:

SELECT
  name
FROM
  image
WHERE name like '%\%'

Results:

|      name |
|-----------|
|        a% |
| blah%blah |

SQL Fiddle

Using utf8_general_ci collation:

CREATE TABLE image
    (`id` int, `name` varchar(55) collate utf8_general_ci)
;

INSERT INTO image
    (`id`, `name`)
VALUES
    (1, 'a%'),
    (2, 'blah%blah')
;

Query 1:

SELECT
  name
FROM
  image
WHERE name like '%\%'

Results:

| name |
|------|
|   a% |

Upvotes: 1

Related Questions