LIKE

USAGE

string LIKE match
string LIKE match ESCAPE escape_char

DETAILS

The LIKE operator matches a wildcard match string against a text string. A 1 (true) is returned if they match, otherwise a 0 (false) is returned.

The operator x LIKE y ESCAPE z behaves in exactly the same manner as the function call LIKE(x, y, z).

The characters % and _ are treated as wildcard characters unless they are escaped:

• % (percent) - matches a string of any length, including nothing

• _ (underscore) - matches any single character

• the escape character (\ by default) - ensures that the following character in the match string is treated as a normal character to match

The optional ESCAPE character clause can specify an alternate character to use. Keep in mind that this new escape character does not change the parsing of the string itself, so it cannot be used to try to escape a string's quoting character.

A NULL is returned if the string or match values are NULL. If the escape_char value is NULL, a syntax error is generated.

EXAMPLES

SELECT 'This is a test' LIKE '%test' AS result1,
       '3' LIKE 3 AS result2;

+---------+---------+
| result1 | result2 |
+---------+---------+
|       1 |       1 |
+---------+---------+

CREATE TABLE like_example (
  description VARCHAR(20),
  example INT
);

INSERT INTO like_example VALUES
  ('Everything', 42),
  ('Dalmations', 101),
  ('Agent', 99),
  ('100% Done', 13),
  ('CPU', 64);

SELECT *
FROM like_example
WHERE description LIKE '%ing'
   OR description LIKE '____t%';

+-------------+---------+
| description | example |
+-------------+---------+
| Everything  |      42 |
| Agent       |      99 |
+-------------+---------+

-- Match descriptions with a percent
SELECT *
FROM like_example
WHERE description LIKE '%\\%%';

+-------------+---------+
| description | example |
+-------------+---------+
| 100% Done   |      13 |
+-------------+---------+

-- Match descriptions with a percent using alternate escape character
SELECT *
FROM like_example
WHERE description LIKE '%=%%' ESCAPE '=';

+-------------+---------+
| description | example |
+-------------+---------+
| 100% Done   |      13 |
+-------------+---------+

CHANGE HISTORY