114

I have to look for a PDF manual using this query:

root@localhost:test> select * from a where name like '%taz_manual%.pdf%';
+--------------------+------------------+-------------+
| name               | description      |        size | 
+--------------------+------------------+-------------+
| taz-manual-1.1.pdf | Manual v1.0 TA-Z |    31351902 |
| taz-manual-0.2.pdf | Manual v1.0 T1-A |     3578278 |
| taz_manual-2.0.pdf | Manual v2.0 GA-X |   542578278 |
etc........
+--------------------+------------------+-------------+
132 row in set (0.00 sec)

Why am I seeing the the one with dashes when I specify the name to be taz_manual%.pdf?

Ben
  • 51,770
  • 36
  • 127
  • 149
E.G.
  • 2,848
  • 3
  • 20
  • 23

2 Answers2

237

Because the underscore _ is a wildcard like the percent %, except that it only looks for one character.

SQL pattern matching enables you to use "_" to match any single character and "%" to match an arbitrary number of characters (including zero characters).

(From section 3.3.4.7. Pattern Matching in the MySQL documentation.)

If you want to use the underscore in like as a literal, you have to escape it:

select * from a where name like '%taz\_manual%.pdf%';
Book Of Zeus
  • 49,509
  • 18
  • 174
  • 171
  • afaik this is only relevant when you are in a pattern context. e.g. inside a `LIKE` statement. When replacing all `_` with an `-` : `UPDATE sys_file set identifier = REPLACE(identifier, '_', '-') WHERE identifier LIKE '%\_%';`. Notice the escaping inside `LIKE` and no escaping inside `REPLACE`. (I find it strange though that you are not in a pattern context inside replace...) – Hafenkranich Jun 30 '16 at 14:52
  • @Hafenkranich from the mysql doc: "use the LIKE or NOT LIKE comparison operators" – Book Of Zeus Jul 17 '16 at 17:19
1

I had a similar issue with space and hyphens while matching strings with exact match:

SELECT id FROM location WHERE name = 'IND - HQ';

The above query didn't return any records in MySQL. I had to escape the spaces and hyphens and use LIKE instead of exact match with equals (=) as follows:

SELECT id FROM location WHERE name LIKE 'IND_\-_HQ';
reformed
  • 4,505
  • 11
  • 62
  • 88
nbs
  • 311
  • 1
  • 12