56

I have the following table images:

+----+--------------+
| id |   img_path   |
+----+--------------+
| 1  | abc_1.jpg    |
| 2  | abc_2.jpg    |
| 3  | abcde_1.jpg  |
| 4  | abcde_2.jpg  |
| 5  | abcdef_1.jpg |
+----+--------------+

I would like to select the entries that img_path starts with abc_, so I use the following query:

SELECT id FROM images WHERE img_path LIKE 'abc_%'

But it returns all 5 rows. How do I only returns id = 1 & 2 ( which img_path starts with abc_) ?

dachi
  • 1,604
  • 11
  • 15
Raptor
  • 53,206
  • 45
  • 230
  • 366

1 Answers1

102

Found out that _ is a special character. Have to escape with backslashes.

SELECT id FROM images WHERE img_path LIKE 'abc\_%'

which returns 2 rows as expected

Raptor
  • 53,206
  • 45
  • 230
  • 366
  • 12
    yes, and a more complete answer is that the underscore matches any single char. – sekmo Sep 26 '20 at 13:49
  • 2
    If you're using Rails / ActiveRecord, you need to double escape, like this: `where( "name LIKE ?", "abc\\_%" )`. – Joshua Pinter Feb 09 '21 at 03:11
  • @JoshuaPinter This has nothing to do with Rails but with the programming language used. Ruby seems to support `'abc\_%'`. – xehpuk Jan 27 '22 at 20:45
  • @xehpuk Well, Rails uses Ruby and `'abc\_%'` wasn't working. So if you're saying pure Ruby supports it then Rails/ActiveRecord must be responsible. – Joshua Pinter Jan 27 '22 at 22:43
  • Proper answer is a bit more complicated, see https://stackoverflow.com/a/66069453/3027445 – podarok Jan 12 '23 at 13:20
  • 1
    @podarok Isn't that for SQL Server? Are you sure the `[]` additional syntax is necessary for mysql as well? I tried and didn't need it. – site80443 Jan 13 '23 at 15:36
  • @site80443 ah, I missed a part about MySQL in question. Yes, my link is for SQL Server, haven't tested this with MySQL, though – podarok Jan 14 '23 at 12:23