Link Search Menu Expand Document

ILIKE

Allows matching of strings based on comparison to a pattern. ILIKE is normally used as part of a WHERE clause.

Syntax

<expr> ILIKE '<pattern>'
Parameter Description
<expr> Any expression that evaluates to a TEXT, STRING, or VARCHAR data type.
<pattern> Specifies the pattern to match and is case-insensitive. SQL wildcards are supported:

* Use an underscore (_) to match any single character
* Use a percent sign (%) to match any number of any characters, including no characters.

Example

For this example, we will create and load data into a demonstration table match_test:

CREATE DIMENSION TABLE match_test (first_name TEXT, last_name TEXT);

INSERT INTO
	match_test
VALUES
	('Sammy', 'Sardine'),
	('Franco', 'Fishmonger'),
	('Carol', 'Catnip'),
	('Thomas', 'Tinderbox'),
	('Deborah', 'Donut'),
	('Humphrey', 'Hoagie'),
	('Frank', 'Falafel');

We can match first names that partially match the string “Fran” and any following characters as follows:

SELECT
	*
FROM
	match_test
WHERE
	first_name ILIKE 'Fran%';

Returns:

+------------+------------+
| first_name | last_name  |
+------------+------------+
| Frank      | Falafel    |
| Franco     | Fishmonger |
+------------+------------+