SQL ILIKE

Любимый оператор ILIKE помогает легко сопоставлять, находить и фильтровать строковые значения по заданному шаблону, используя SQL подстановочные знаки без учета регистра. Если вы приверженец чувствительности к регистру, не стесняйтесь использовать не менее важный оператор LIKE 😆

Как использовать оператор SQL ILIKE

Оператор ILIKE имеет простой синтаксис и может использоваться в предложениях WHERE или в конструкциях case:

where <field_name> ilike '<pattern>' или case when <field_name> ilike '<pattern>'

Некоторые заметки о синтаксисе и функциональности этого оператора:

• <pattern> может использовать два SQL подстановочных знака (% и _); подчеркивание соответствует любому одиночному символу, а % соответствует нулю или более символам
  • Пример: '%j' = любая строка, заканчивающаяся на букву j
  • Пример: 'j%' = любая строка, начинающаяся с буквы j
  • Пример: 'j%l' = любая строка, начинающаяся с буквы j и заканчивающаяся на букву l
  • Пример: '_j%' = любая строка, в которой буква j находится на второй позиции
• В большинстве случаев использования оператора ILIKE, вероятно, будет задействован подстановочный знак %
• Оператор ILIKE нечувствителен к регистру, что означает, что регистр в <pattern>, который вы хотите отфильтровать, не должен совпадать с регистром в значениях вашего столбца
• Оператор ILIKE может быть использован с оператором NOT для фильтрации строк, которые не соответствуют заданному шаблону

Давайте рассмотрим практический пример использования оператора ILIKE.

Пример SQL ILIKE

select
   payment_id,
   order_id,
   payment_method,
   case when payment_method ilike '%card' then 'card_payment' else 'non_card_payment' end as was_card
from {{ ref('payments') }}

Этот простой запрос, использующий таблицу payments из Jaffle Shop, создает новый столбец для определения, использовался ли тип оплаты картой (например, дебетовая карта, кредитная карта, подарочная карта) на основе того, заканчивается ли значение payment_method на card:

payment_id	order_id	payment_method	was_card
1	1	credit_card	card_payment
9	9	gift_card	card_payment
3	3	coupon	non_card_payment
4	4	coupon	non_card_payment

Синтаксис ILIKE в Snowflake, Databricks, BigQuery и Redshift

Большинство современных хранилищ данных, за исключением Google BigQuery, поддерживают оператор ILIKE, и синтаксис одинаков для всех. Используйте таблицу ниже, чтобы узнать больше о документации для оператора ILIKE в вашем хранилище данных.

Хранилище данных	Поддержка ILIKE?
Snowflake	✅
Databricks	✅
Amazon Redshift	✅
Google BigQuery	❌, рекомендуется использовать регулярные выражения или функцию CONTAINS

Примеры использования оператора ILIKE

Оператор ILIKE имеет очень похожие случаи использования с оператором LIKE, поэтому мы не будем повторяться здесь. Важно понимать, как выглядят вариации регистра в ваших данных при использовании операторов LIKE или ILIKE: если регистр в столбце непоследователен, ILIKE будет вашим другом; если ваши бэкенд-инженеры и аналитики строго следуют стилевому руководству (и наши исходные данные волшебным образом имеют одинаковый регистр), оператор LIKE будет к вашим услугам, если он вам понадобится.