現代のビジネスにおいて、データベースに蓄積された膨大な情報から必要なデータを特定するスキルは非常に重要です。
リレーショナルデータベース(RDB)を操作するための言語であるSQLの中でも、データの取得を担うSELECT文は最も頻繁に使用される構文の一つです。
特に、大量のテキストデータの中から特定の文字列が含まれているレコードや、逆に特定の条件を「含まない」レコードを効率的に抽出するテクニックは、実務において欠かせません。
本記事では、SQLのSELECT文を用いて特定の文字列を抽出する基本的な方法から、高度なフィルタリング手法までを詳しく解説します。
SELECT文とWHERE句による基本的な文字列抽出
データベースから特定のデータを取り出す際、基本となるのがSELECT文とWHERE句の組み合わせです。
WHERE句は、抽出するレコードが満たすべき条件を指定するために使用されます。
最も単純な抽出方法は、比較演算子である = を使用した完全一致検索です。
例えば、社員テーブルから名前が「田中」というデータのみを取得したい場合に利用します。
しかし、実際の業務では「特定の文字から始まるもの」や「特定の単語を含むもの」といった曖昧な条件で検索を行いたい場面が多くあります。
そのような場合に活用されるのが、LIKE演算子を用いたパターンマッチングです。
LIKE演算子とワイルドカードの活用
LIKE演算子を使用すると、特定のパターンに一致する文字列を柔軟に検索することが可能になります。
この際、パターンの代わりとして機能する特別な文字を「ワイルドカード」と呼びます。
SQLで一般的に使用されるワイルドカードには、% (パーセント)と _ (アンダースコア)の2種類があります。
% は、0文字以上の任意の文字列を意味します。
一方で、_ は、ちょうど1文字の任意の文字を意味します。
これらの組み合わせにより、前方一致、後方一致、部分一致といった検索を実現できます。
前方一致検索の例
前方一致検索は、特定の文字列で始まるデータを抽出する際に使用します。
例えば、商品名が「スマホ」で始まるレコードをすべて取得したい場合は、以下のようなSQLを記述します。
-- 商品名が「スマホ」で始まるデータを抽出
SELECT product_id, product_name
FROM products
WHERE product_name LIKE 'スマホ%';product_id | product_name
-----------+--------------
101 | スマホケース
102 | スマホスタンド
103 | スマホ充電器この例では、スマホ% と指定することで、後ろにどのような文字が続いても、「スマホ」から始まるデータがすべて該当します。
部分一致検索の例
文字列のどこかに特定の単語が含まれているかどうかを判定するには、部分一致検索を用います。
検索したいキーワードの前後を % で囲むことで実現可能です。
-- 商品名に「限定」という文字が含まれるデータを抽出
SELECT product_id, product_name
FROM products
WHERE product_name LIKE '%限定%';product_id | product_name
-----------+------------------
205 | 夏季限定スイーツ
208 | 限定モデル腕時計
310 | 数量限定セール品部分一致検索は非常に強力ですが、データ量が多い場合には検索速度が低下する可能性があるため、注意が必要です。
特定の文字列を「含まない」データの抽出方法
データベース操作において、特定の条件に合致するものを抽出するだけでなく、特定の文字列を含まないデータのみを除外して取得したいケースも多々あります。
不必要な情報をフィルタリングし、必要なデータだけに絞り込むことは、分析の精度を高めるために不可欠です。
SQLでは、この「含まない」という条件を指定するために NOT LIKE 演算子や比較演算子を使用します。
NOT LIKE演算子による除外検索
NOT LIKE 演算子を使用すると、指定したパターンに一致しないレコードを抽出できます。
例えば、顧客リストからメールアドレスに「example.com」が含まれていない顧客だけを抽出したい場合に非常に便利です。
-- ドメインに「example.com」を含まないユーザーを抽出
SELECT user_id, email
FROM users
WHERE email NOT LIKE '%example.com';user_id | email
--------+--------------------
001 | tanaka@test.jp
002 | sato@gmail.com
005 | suzuki@outlook.jpこのように記述することで、特定のキーワードを含むノイズデータを一括で排除することができます。
条件が複数ある場合は、AND や OR を組み合わせてさらに複雑な除外条件を作成することも可能です。
比較演算子を使った不一致の指定
特定の文字列と完全に一致しないものを探す場合は、!= または <> 演算子を使用します。
これは、パターンマッチングではなく「値そのものが異なること」を条件とする場合に使用されます。
-- ステータスが「完了」ではない注文を表示
SELECT order_id, status
FROM orders
WHERE status != '完了';このクエリは、ステータスが「完了」以外の「未着手」「進行中」「キャンセル」といったすべてのレコードを返します。
高度な文字列操作関数による抽出
単純なパターンマッチングだけでは対応できない複雑な要件がある場合、SQLの文字列操作関数を利用します。
データベースの種類(MySQL, PostgreSQL, Oracle, SQL Serverなど)によって関数の名称が異なる場合がありますが、基本的な考え方は共通しています。
SUBSTR関数/SUBSTRING関数による部分抽出
文字列の特定の位置から指定した文字数分だけを切り出すには、SUBSTR 関数(または SUBSTRING 関数)を使用します。
例えば、会員番号の最初の2文字が「JP」であるレコードを抽出しつつ、その2文字目以降を別の用途で使いたい場合などに有効です。
-- 会員IDの先頭2文字が「ID」であるレコードを抽出
SELECT user_id, user_name
FROM members
WHERE SUBSTR(user_id, 1, 2) = 'ID';この関数をWHERE句で使用することで、文字の位置に基づいた精密なフィルタリングが可能になります。
INSTR関数/CHARINDEX関数による位置特定
特定の文字列が、対象の列の中の何文字目に出現するかを数値で返すのが INSTR 関数です。
もし指定した文字列が含まれていない場合、この関数は 0 を返します。
これを利用して、「特定の文字が2回目以降に出現するもの」といった特殊な抽出条件を作ることができます。
-- 「-」(ハイフン)が含まれているデータのみを抽出
-- (ハイフンの位置が0より大きいものを探す)
SELECT product_code
FROM inventory
WHERE INSTR(product_code, '-') > 0;この手法は、LIKE '%-%' と同様の結果を得られますが、関数の戻り値を利用して計算を行いたい場合に重宝します。
正規表現を用いた柔軟な抽出
さらに複雑な文字列パターンを指定したい場合には、正規表現(Regular Expression)を使用します。
正規表現を使えば、「数字3桁で始まり、その後にアルファベットが続く」といった高度な条件を1つの式で表現できます。
多くのモダンなデータベースでは、REGEXP や ~ 演算子、あるいは REGEXP_LIKE 関数をサポートしています。
-- MySQLで「A」または「B」で始まる5桁の郵便番号のようなパターンを抽出
SELECT post_code
FROM addresses
WHERE post_code REGEXP '^[AB][0-9]{4}$';正規表現による検索は非常に強力ですが、構文が複雑になりやすいため、チーム開発では可読性に配慮する必要があります。
また、計算リソースを多く消費するため、実行頻度の高いクエリで使用する際はインデックスの効き方に注意を払うべきです。
文字列抽出における注意点とパフォーマンス
文字列の抽出条件を設定する際には、いくつか注意すべき重要なポイントがあります。
これを怠ると、意図しない検索結果になったり、データベース全体の動作を重くしてしまったりする原因になります。
大文字・小文字の区別(Case Sensitivity)
データベースのデフォルト設定によっては、大文字と小文字が区別される場合があります。
例えば、PostgreSQLでは LIKE は大文字小文字を区別しますが、MySQLのデフォルトの照合順序(Collation)では区別されないことが多いです。
確実に区別せずに検索したい場合は、UPPER 関数や LOWER 関数を使用して、比較対象を統一する方法が一般的です。
-- 大文字小文字を無視して「apple」を含むデータを検索
SELECT *
FROM fruits
WHERE LOWER(name) LIKE '%apple%';このように記述することで、「Apple」「APPLE」「apple」のすべてを正しく抽出できます。
インデックスの有効活用
大量のデータを扱うテーブルで、WHERE column LIKE '%キーワード%' (中間一致)を使用すると、インデックスが効かなくなり、全件走査(Full Table Scan)が発生します。
全件走査が行われると、検索に時間がかかりシステム全体のパフォーマンスが低下します。
検索を高速化したい場合は、可能な限り「前方一致(キーワード%)」を使用するか、全文検索エンジン(ElasticsearchやMroongaなど)の導入を検討してください。
前方一致であれば、通常のB-treeインデックスを利用して高速に目的のデータにアクセスすることが可能です。
NULL値の扱い
文字列抽出を行う列に NULL が含まれている場合、通常の比較演算子や LIKE 演算子では NULL のレコードは抽出対象になりません。
NOT LIKE を使用した場合でも、NULL は条件に一致しないと判定され、結果に含まれないことが一般的です。
もし NULL のデータも考慮に入れる必要がある場合は、IS NULL や COALESCE 関数を使用して適切に処理を行う必要があります。
実務で役立つ具体的な文字列抽出ケーススタディ
ここでは、実際の業務シーンでよく遭遇する文字列抽出の具体的な活用例をいくつか紹介します。
特定の拡張子を持つファイル名を除外する
ファイル管理システムのログから、画像ファイル以外のログを抽出したい場合を想定します。
SELECT log_id, file_name
FROM access_logs
WHERE file_name NOT LIKE '%.jpg'
AND file_name NOT LIKE '%.png'
AND file_name NOT LIKE '%.gif';このように複数の NOT LIKE を組み合わせることで、不要な拡張子のデータを綺麗に取り除くことができます。
特定のフォーマットに従わない異常データの検知
例えば、電話番号の列に数字とハイフン以外の文字が入ってしまっている不正なデータを特定したい場合です。
-- 数字とハイフン以外の文字が含まれるレコードを抽出(PostgreSQLの正規表現例)
SELECT customer_id, phone_number
FROM customers
WHERE phone_number ~ '[^0-9-]';データのクレンジング作業において、このような文字列抽出テクニックは非常に重宝されます。
まとめ
SQLのSELECT文を用いた特定の文字列抽出は、データベース操作における基本中の基本であり、最も応用範囲が広い技術です。
基本的な LIKE 演算子によるパターンマッチングから、特定の条件を排除する NOT LIKE 演算子の活用、さらには高度な文字列関数や正規表現までを使い分けることが求められます。
特に、「何を含まないか」という否定の条件を適切に設定することで、膨大なデータから真に価値のある情報だけを効率よく手に入れることができます。
検索パフォーマンスへの影響やNULL値の挙動といった注意点を踏まえつつ、最適なクエリを作成できるよう練習を重ねていきましょう。
正確な文字列操作スキルを身につけることは、データエンジニアやアナリストとしてのキャリアにおいて強力な武器となるはずです。