LIKE()

USAGE

LIKE(check, match, escape)

DETAILS

LIKE() is a function that matches a wildcard match string against a check string. A 1 (true) is returned if they match, otherwise a 0 (false) is returned.

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

The wildcard expression is required to match the full ''check`` value.

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 - ensures that the following character in the match string is treated as a normal character to match

Any non-string arguments are converted to their string representation.

A NULL is returned if either of the first two arguments are NULL. If the third argument is a NULL, the Xpand server crashes.

EXAMPLES

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

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

SELECT LIKE('Hello there', 'Hello', '#');

+-----------------------------------+
| LIKE('Hello there', 'Hello', '#') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

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 LIKE(description, '%\\%%', '\\');

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

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

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

CHANGE HISTORY