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 for a 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

• \\ (backslash) - the default escape character

  • Ensures that the following character in the match string is treated as a normal character that must be matched

  • Can be changed to another character using the optional ESCAPE character clause

  • When using backslash as the escape character, it must often be doubled-up in order to be parsed as a single backslash

  • Keep in mind that a 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