CODEWITHSUNDEEP
mysqlsql
Raptor
Raptor

Reputation: 54212

MySQL LIKE query with underscore

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

Upvotes: 58

Views: 29597

Answers (2)

A-Tech
A-Tech

Reputation: 1101

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.

Upvotes: -1

Raptor
Raptor

Reputation: 54212

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

Upvotes: 113

Related Questions