LIKE句とワイルドカードでスマート検索！SQLite部分ストリング検索の極意
SQLiteで部分ストリングを含む列を選択する方法
例
customersテーブルに、顧客名とメールアドレスを含む列があるとします。このテーブルから、メールアドレスに "example.com" を含むすべての顧客のレコードを選択するには、次のクエリを使用します。
SELECT * FROM customers
WHERE email LIKE '%example.com%';
このクエリは、email 列の値が %example.com% パターンに一致するすべての行を返します。% 記号はワイルドカードであり、任意の文字列に一致することを意味します。つまり、このクエリは、[email protected] や [email protected] などのメールアドレスを持つ顧客だけでなく、[email protected] や [email protected] などのメールアドレスを持つ顧客もすべて返します。
ワイルドカードの使い方
%: 任意の文字列に一致します。[char1, char2, ...]: 指定された文字のいずれかに一致します。
以下のクエリは、次の条件に一致する顧客レコードをすべて返します。
- 名前が "John" で始まる
- メールアドレスが "@example.com" で終わる
SELECT * FROM customers
WHERE name LIKE 'John%'
AND email LIKE '%@example.com';
LIKE 句は、他の演算子と組み合わせて使用することができます。例えば、以下のクエリは、次の条件に一致する顧客レコードをすべて返します。
- 注文数が 10 件以上
SELECT * FROM customers
WHERE name LIKE 'John%'
AND email LIKE '%@example.com'
AND orders >= 10;
LIKE 句とワイルドカードを使用すると、SQLiteで部分ストリングを含む列を簡単に選択できます。この方法は、顧客データ、製品データ、その他のテキストベースのデータを操作する際に役立ちます。
-- customersテーブルを作成
CREATE TABLE customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL
);
-- サンプルデータ挿入
INSERT INTO customers (name, email) VALUES
('John Doe', '[email protected]'),
('Jane Doe', '[email protected]'),
('Peter Jones', '[email protected]'),
('Mary Smith', '[email protected]'),
('John Williams', '[email protected]');
-- メールアドレスに "example.com" を含む顧客をすべて選択
SELECT * FROM customers
WHERE email LIKE '%example.com%';
-- 名前が "John" で始まる顧客をすべて選択
SELECT * FROM customers
WHERE name LIKE 'John%';
-- メールアドレスが "@example.com" で終わり、注文数が 10 件以上である顧客をすべて選択
SELECT * FROM customers
WHERE email LIKE '%@example.com'
AND orders >= 10;
- customersテーブルの作成: このクエリは、
id列（主キーと自動インクリメント）、name列（テキスト型、NULL不可）、email列（テキスト型、NULL不可）を持つcustomersテーブルを作成します。 - サンプルデータの挿入: このクエリは、
customersテーブルに 5 件のサンプルデータ行を挿入します。 - 部分ストリングを含む列の選択: このクエリは、
email列に "example.com" を含むすべての顧客のレコードを選択します。 - 接頭辞を含む列の選択: このクエリは、名前が "John" で始まるすべての顧客のレコードを選択します。
- 条件を満たす列の選択: このクエリは、メールアドレスが "@example.com" で終わり、注文数が 10 件以上であるすべての顧客のレコードを選択します。
このコードを参考に、LIKE 句とワイルドカードを使用して、SQLiteで部分ストリングを含む列を簡単に選択する方法を理解してください。
SQLiteで部分ストリングを含む列を選択するその他の方法
FTS5 モジュールを使用する
SQLiteには、全文検索を可能にする FTS5と呼ばれる拡張モジュールが用意されています。FTS5 を使用すると、部分ストリングだけでなく、フレーズや接尾辞による検索など、より高度なテキスト検索を実行することができます。
以下のクエリは、customers テーブル内の name 列と email 列で "John" を含むすべてのレコードを検索します。
SELECT * FROM customers
WHERE fts5(name, email) MATCH 'John';
REGEXP 関数を使用する
SQLiteには、正規表現によるパターンマッチングに使用できる REGEXP 関数があります。REGEXP 関数を使用して、より複雑な部分ストリング検索を実行することができます。
以下のクエリは、email 列が ^[a-z0-9]+@[a-z0-9]+\.[a-z]{2,}$ パターンに一致するすべての顧客レコードを返します。このパターンは、有効なメールアドレス形式を表します。
SELECT * FROM customers
WHERE REGEXP(email, '^[a-z0-9]+@[a-z0-9]+\.[a-z]{2,}$');
仮想テーブルを使用する
SQLiteでは、仮想テーブルを使用して、カスタムの検索ロジックを実装することができます。この方法は、より複雑な部分ストリング検索要件がある場合に役立ちます。
以下のコードは、customer_search という名前の仮想テーブルを作成します。この仮想テーブルは、name 列と email 列で部分ストリング検索を実行することができます。
CREATE VIRTUAL TABLE customer_search USING FTS5(
name TEXT,
email TEXT
);
INSERT INTO customer_search
SELECT name, email FROM customers;
この仮想テーブルを作成したら、以下のクエリを使用して部分ストリング検索を実行することができます。
SELECT * FROM customer_search
WHERE name MATCH 'John' OR email MATCH 'John';
SQLiteで部分ストリングを含む列を選択するには、さまざまな方法があります。それぞれの方法には長所と短所があるため、要件に応じて適切な方法を選択する必要があります。
- シンプルで使いやすい場合は、
LIKE句とワイルドカードがおすすめです。 - より高度なテキスト検索が必要な場合は、
FTS5モジュールまたはREGEXP関数を使用します。 - 複雑な検索ロジックが必要な場合は、仮想テーブルを使用します。
sqlite
