LIKE

USAGE

string LIKE match
string LIKE match ESCAPE escape_char

Value Name

Description

string

The string to search

match

The wildcard pattern used to search

escape_char

The escape character to use to disable wildcard characters

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

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

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 |
+-------------+---------+

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

11.4 Enterprise

  • Not present.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

11.4 Enterprise

  • Not present.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES