SQLのLIKE検索で大文字小文字や文字コード、キャメルケースの違いを無視する方法

SQLのLIKE句を使用してデータベース内のテキストを検索する際、大文字小文字や文字コードの違いを無視して検索することがあります。これは、ユーザーが検索クエリを行う際に、入力の一貫性を保ちつつ、柔軟な検索を実現するためです。本記事では、そのようなケースでの検索クエリの書き方について解説します。

ユースケース

検索条件の柔軟性を活かして、異なる表記や形式で記述されたデータを取得するための具体的なユースケースを以下に示します。

ユースケース1: ユーザー検索

検索キーワードとして「JohnDoe」を入力した場合、以下のような検索結果が得られます。

ユーザーID |ユーザー名 | メールアドレス
1 | JohnDoe | john@example.com
2 | JOHNdOE | john@gmail.com
3 | johndoe | jdoe@example.com

ユースケース2: 商品検索

検索キーワードとして「アップル」を入力した場合、以下のような検索結果が得られます。

商品ID | 商品名 | 説明
1 | あっぷる | 新鮮なりんごを毎日お届けします。
2 | アップル | みずみずしいアップルをご堪能ください。
3 | あっプル | このりんごはとても美味しいです。

SQLのクエリ

SELECT * FROM table_name 
WHERE CONCAT(LOWER(column_name1), IFNULL(LOWER(column_name2), '')) 
LIKE LOWER('%search_term%') COLLATE utf8_unicode_ci;

utf8_unicode_ciかutf8mb4_unicode_ciを`COLLATE`の後に入れてください
これはデータベースの仕様によって変わります。

SQLのLIKE句とは

SQLのLIKE句は、テーブル内のデータを部分一致で検索するための構文です。通常、LIKE句では文字列の一部に一致するレコードを検索します。しかし、検索の際に大文字小文字や文字コードの違いを無視したい場合もあります。そのような場合には、特定の手法を使用して検索条件を作成する必要があります。

大文字小文字の無視

大文字小文字の無視を実現するためには、検索対象の文字列と検索語をすべて同じケース（例えばすべて小文字）に変換してから比較する方法があります。これにより、大文字小文字の違いを無視して検索を行うことができます。

SELECT * FROM table_name 
WHERE LOWER(column_name) LIKE LOWER('%search_term%');

このクエリでは、LOWER() 関数を使用して検索対象の列と検索語をすべて小文字に変換し、それらをLIKE句で比較しています。この方法を使うことで、大文字小文字を区別せずに検索を行うことができます。

文字コードの無視

文字コードの違いを無視するためには、検索対象の文字列に対して適切な文字コードを指定する必要があります。特に、ひらがなとカタカナのように文字コードが異なる文字を含む場合には、文字コードの指定が重要です。

SELECT * FROM table_name 
WHERE column_name LIKE N'%アップル%' COLLATE utf8_unicode_ci;

このクエリでは、COLLATE utf8_unicode_ci (utf8mb4_unicode_ci)を使用して検索対象の列に対してUTF-8文字コードを指定しています。これにより、ひらがなとカタカナの区別を無視した検索が可能になります。

以上のように、SQLのLIKE句を使用して大文字小文字や文字コードの違いを無視して検索する方法について解説しました。これらの手法を使うことで、ユーザーが柔軟な検索を行うことができます。

「DB Fiddle」で検証

https://www.db-fiddle.com/

入力した、このURLは公開されるので、保存するクエリなどにはご注意ください！

Schema SQL に以下を入力

CREATE TABLE table_name (
  `id` INTEGER,
  `column_name1` VARCHAR(11),
  `column_name2` VARCHAR(125)
);

INSERT INTO table_name
  (`id`, `column_name1`, `column_name2`)
VALUES
  ('1', 'JohnDoe', 'John@example.com'),
  ('2', 'JOHNdOE', 'John@example.com'),
  ('3', 'johndoe', 'jdoe@example.com');

Query SQL に以下を入力

SELECT * FROM table_name 
WHERE CONCAT(LOWER(column_name1), IFNULL(LOWER(column_name2), '')) 
LIKE LOWER('%JohnDoe%') COLLATE utf8mb4_unicode_ci;