How the LIKE Operator Works in MySQL

In MySQL, the LIKE operator performs pattern matching using an SQL pattern.

If the string matches the pattern provided, the result is 1, otherwise it’s 0.

The pattern doesn’t necessarily need to be a literal string. This function can be used with string expressions and table columns.

Syntax

The syntax goes like this:

expr LIKE pat [ESCAPE 'escape_char']

Where expr is the input string and pat is the pattern for which you’re testing the string against.

The optional ESCAPE clause allows you to specify an escape character. The default escape character is \, so you can omit this clause if you don’t need to change this.

Example 1 – Basic Usage

Here’s an example of how to use this operator in a SELECT statement:

SELECT 'Charlie' LIKE 'Char%';

Result:

+------------------------+
| 'Charlie' LIKE 'Char%' |
+------------------------+
|                      1 |
+------------------------+

In this case, the return value is 1 which means that the input string matched the pattern. In particular, we specified that the input string should start with Char and end with anything. The % character matches any number of characters (including zero characters).

Here’s what happens if we drop the %:

SELECT 'Charlie' LIKE 'Char';

Result:

+-----------------------+
| 'Charlie' LIKE 'Char' |
+-----------------------+
|                     0 |
+-----------------------+

The return result is 0 which means no match. This is because we didn’t use a wildcard character to specify any other characters.

Example 2 – The _ Wildcard

We also have the option of using the _ wildcard character to specify only a single character. Here’s an example:

SELECT 'Charlie' LIKE 'Ch_rlie';

Result:

+--------------------------+
| 'Charlie' LIKE 'Ch_rlie' |
+--------------------------+
|                        1 |
+--------------------------+

The two wildcard characters can be combined within a pattern if required:

SELECT 'Charlie likes donuts' LIKE 'Ch_rlie%' AS 'Result';

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

Here are some more:

SELECT 
  'Charlie likes donuts' LIKE 'Ch_rlie%donuts' AS 'Result 1',
  'Charlie likes donuts' LIKE 'Ch_rlie%nuts' AS 'Result 2',
  'Charlie likes donuts' LIKE 'Ch%rlie %likes %' AS 'Result 3',
  'Charlie likes donuts' LIKE '% likes %' AS 'Result 4';

Result:

+----------+----------+----------+----------+
| Result 1 | Result 2 | Result 3 | Result 4 |
+----------+----------+----------+----------+
|        1 |        1 |        1 |        1 |
+----------+----------+----------+----------+

Let’s make some changes to that example so that we can see some examples of when they don’t match:

SELECT 
  'Charlie likes donuts' LIKE 'Ch%rlie_donuts' AS 'Result 1',
  'Charlie likes donuts' LIKE 'Charlie_nuts' AS 'Result 2',
  'Charlie likes donuts' LIKE 'Charlie _likes donuts' AS 'Result 3',
  'Charlie likes donuts' LIKE '_ likes _' AS 'Result 4';

Result:

+----------+----------+----------+----------+
| Result 1 | Result 2 | Result 3 | Result 4 |
+----------+----------+----------+----------+
|        0 |        0 |        0 |        0 |
+----------+----------+----------+----------+

Example 3 – A Database Example

The LIKE operator is often used within a WHERE clause of a SELECT statement when querying a database. When it’s used in this fashion, it narrows down the results to only those records that match, but we see the actual results (not just a 1 or 0).

Here’s an example of how we can use this operator within a database query:

SELECT ArtistId, ArtistName
FROM Artists
WHERE ArtistName LIKE 'B%';

Result:

+----------+----------------+
| ArtistId | ArtistName     |
+----------+----------------+
|        4 | Buddy Rich     |
|       11 | Black Sabbath  |
|       15 | Birds of Tokyo |
|       16 | Bodyjar        |
+----------+----------------+

In this case, it was a simple query that returns all artists whose names start with the letter B.

Here’s the full list of artists in that table:

SELECT ArtistId, ArtistName
FROM Artists;

Result:

+----------+------------------------+
| ArtistId | ArtistName             |
+----------+------------------------+
|        1 | Iron Maiden            |
|        2 | AC/DC                  |
|        3 | Allan Holdsworth       |
|        4 | Buddy Rich             |
|        5 | Devin Townsend         |
|        6 | Jim Reeves             |
|        7 | Tom Jones              |
|        8 | Maroon 5               |
|        9 | The Script             |
|       10 | Lit                    |
|       11 | Black Sabbath          |
|       12 | Michael Learns to Rock |
|       13 | Carabao                |
|       14 | Karnivool              |
|       15 | Birds of Tokyo         |
|       16 | Bodyjar                |
+----------+------------------------+

Example 4 – Escaping with the Backslash Character

What happens if one of the wildcard characters are in your input string and you need to perform a match against it? You can escape it with the backslash character (\). Here’s an example of such a search with and without the escape character:

SELECT 
  'usr_123' LIKE 'usr_123' AS 'Without escape',
  'usr_123' LIKE 'usr\_123' AS 'With escape';

Result:

+----------------+-------------+
| Without escape | With escape |
+----------------+-------------+
|              1 |           1 |
+----------------+-------------+

In this case, they both matched, but for different reasons. The first line matched because the wildcard specified that any character will match. The second line also matched, but only because the input string happened to have an underscore in the right place.

Let’s change the input string slightly so that we get a different result:

SELECT 
  'usr+123' LIKE 'usr_123' AS 'Without escape',
  'usr+123' LIKE 'usr\_123' AS 'With escape';

Result:

+----------------+-------------+
| Without escape | With escape |
+----------------+-------------+
|              1 |           0 |
+----------------+-------------+

The unescaped version returned positive, because the wildcard meant that we could have any character in that spot. The escaped version explicitly stated that only the underscore character (_) will match. The input string didn’t have an underscore character in that spot and so the result was negative.

Example 5 – The ESCAPE Clause

You can also use the ESCAPE clause to specify your own custom escape character. Here’s an example:

SELECT 
  'usr_123' LIKE 'usr|_123' ESCAPE '|' AS 'String 1',
  'usr+123' LIKE 'usr|_123' ESCAPE '|' AS 'String 2';

Result:

+----------+----------+
| String 1 | String 2 |
+----------+----------+
|        1 |        0 |
+----------+----------+

Example 6 – Numeric Expressions

The MySQL implementation of the LIKE operator allows numeric expressions to be used. Here’s an example:

SELECT 
  1234 LIKE '12%',
  1234 LIKE '12_';

Result:

+-----------------+-----------------+
| 1234 LIKE '12%' | 1234 LIKE '12_' |
+-----------------+-----------------+
|               1 |               0 |
+-----------------+-----------------+

How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?

Consider this QUERY 

(SELECT * FROM `titles` where title = 'test\\')
UNION ALL
(SELECT * FROM `titles` where title LIKE 'test\\\\')

Output:

| ID | TITLE |
--------------
|  1 | test\ |
|  1 | test\ |

QUESTION:

Why no extra (\) required for (=) but for (like) additional \\ is required? Its clear that MySQL escaped the (test\) with (test\\) then using (test\\\\) is logical for LIKE.

Table information:

CREATE TABLE IF NOT EXISTS `titles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `titles`
--

INSERT INTO `titles` (`id`, `title`) VALUES
(1, 'test\\');

 Answers

\ functions as an escape character in LIKE by default.

From the manual for LIKE:

Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

You can change this by specifying another escape character, as in:

SELECT * FROM `titles` where title LIKE 'test\\' ESCAPE '|'

---

LIKE accepts two wildchar characters, % and _.

To be able to match these characters, escaping can be used: \%, \_. This also means that if you want to match \, it has to be escaped as well.

All this is documented in the manual.

MySQL does not display the shortest result first

We have a standard MySQL 5.1 database with a table named places. We have two columns, name and name_bg, where name is the english name of the place and name_bg is the bulgarian name of the place.

We have two places that starts with "РИМ". "Рим" and "Римини".

+--------+--------+--------------+
| id     | name   | name_bg      |
+--------+--------+--------------+
| 221543 | Rimini | Римини       |
|  34514 | Rome   | Рим          |
+--------+--------+--------------+

When searching like this:

select id, name from places where name like 'рим%';

Or like this:

select id, name from places where name_bg like 'рим%';

Everything looks fine.

The weird stuff is when searching like this (the GROUP BY clause):

SELECT places.name, places.name_bg, places.country_id, countries.continent_id WHERE places.name LIKE 'рим%' OR places.name_bg LIKE 'рим%' ORDER ("CHAR_LENGTH(places.name), CHAR_LENGTH(places.name_bg), countries.continent_id, places.popular DESC") GROUP BY country_id LIMIT 10

The query returns Римини first instead of Рим.

We have tryed order and group on the query but nothing helped.

Even switched the IDs of the places but the same result occured.

We want to have the shortes result first.

Any help is appriciated.

Best, Yavor

---

It's not clear to me why you expect that it should. Does the length function do what you need?

SELECT id,
       name
FROM   places
WHERE  name_bg LIKE 'рим%'
        OR name LIKE 'рим%'
ORDER  BY Length(name)




MySQL LIKE query does not pull similar data

I have a simple MySQL-based search script, I will post only the part relevant to the problem below.

$name = 'John Doe';
$query = 'SELECT username FROM members WHERE name LIKE %$name%';

Now, the problem is when I search for John Doe instead of getting the user with that particular name, I get all users named John and Doe and, the funny thing is that, it does not even put John Doe at the top, meaning... you could find John xxxx before that.

Is there and advanced MySQL attribute to accomplish this task?

---

change

$query = 'SELECT username FROM members WHERE name LIKE %$name%';

to

$query = 'SELECT username FROM members WHERE name LIKE "'.%$name%.'"';

or

$query = "SELECT username FROM members WHERE name LIKE '%$name%'";

note, that changing to

$query = 'SELECT username FROM members WHERE name LIKE "%$name%"';

will NOT do the trick.

This is because single quoted strings do not interpret variables.

Note: Unlike the double-quoted and heredoc syntaxes, variables and escape sequences for special characters will not be expanded when they occur in single quoted strings.

Double quoted strings however, will.

The most important feature of double-quoted strings is the fact that variable names will be expanded. See string parsing for details.

Mysql: Display rows from only a certain category of a MySQL database

<?php
//connect to server with username and password, this is the default settings
//when MySQL is installed on Windows XP(Not recommended)
$connection = mysql_connect ("localhost","root", "") or die ("Cannot make the connection");
//connect to database
$db = mysql_select_db ("test",$connection) or die ("Cannot connect to database");
//our SQL query
$sql_query = "SELECT * FROM test WHERE cat like 'search'";
//store the SQL query in the result variable
$result = mysql_query($sql_query);
if(mysql_num_rows($result))
{
//output as long as there are still available fields
while($row = mysql_fetch_row($result))
{
echo ("<a href=\"$row[2]\">$row[3]</a>");
echo (": $row[4]<br>");
}
}
//if no fields exist
else
{
echo "no values in the database";
}

?>