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

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

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES