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