MySQL LIKE query with underscore

Question

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_) ?

ANSI SQL standard solution is to add ESCAPE, e.g. WHERE img_path LIKE 'abc/_%' ESCAPE '/' here we declare / as an escape symbol and use it to escape _ — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Jan 23 at 15:32

Raptor · Accepted Answer · 2014-03-04 09:15:11Z

116

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

edited Mar 4, 2014 at 9:15

answered Mar 4, 2014 at 8:58

Raptor

54.4k47 gold badges254 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Francesco Mari Over a year ago

yes, and a more complete answer is that the underscore matches any single char.

Joshua Pinter Over a year ago

If you're using Rails / ActiveRecord, you need to double escape, like this: where( "name LIKE ?", "abc\\_%" ).

xehpuk Over a year ago

@JoshuaPinter This has nothing to do with Rails but with the programming language used. Ruby seems to support 'abc\_%'.

Joshua Pinter Over a year ago

@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.

site80443 Over a year ago

@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.

|

A-Tech · Accepted Answer · 2025-01-23 15:22:59Z

As per the official documentation for MySQL 8.0:

With LIKE you can use the following two wildcard characters in the pattern:

% matches any number of characters, even zero characters.

_ matches exactly one character.
mysql> SELECT 'David!' LIKE 'David_';
        -> 1 
mysql> SELECT 'David!' LIKE '%D%v%';
        -> 1 
To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, \ is assumed, unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. In that case, no escape character is used.

\% matches one % character.

\_ matches one _ character.
mysql> SELECT 'David!' LIKE 'David\_';
        -> 0
mysql> SELECT 'David_' LIKE 'David\_';
        -> 1 
To specify a different escape character, use the ESCAPE clause:
mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1 
The escape sequence should be one character long to specify the escape character, or empty to specify that no escape character is used. The expression must evaluate as a constant at execution time. If the NO_BACKSLASH_ESCAPES SQL mode is enabled, the sequence cannot be empty.

Collectives™ on Stack Overflow

MySQL LIKE query with underscore

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related