導入
データベースを運用していると、「住所から都道府県だけを取り出したい」「コード番号の一部を切り出して集計したい」といった加工が必要になる場面が頻繁にあります。そんな時に欠かせないのが substr関数 です。この関数を使いこなすことで、アプリケーション側でデータを加工する手間を省き、SQL単体で必要な情報だけを抽出できるため、クエリの柔軟性が劇的に向上します。
基礎知識
substr関数とは、対象となる文字列の中から、指定した開始位置から特定の長さ分を切り出すための関数です。SQLiteにおける基本的な書式は以下の通りです。
substr(文字列, 開始位置, 文字の長さ)
重要なポイントは、開始位置が「0」ではなく「1」から始まるという点です。また、3番目の引数（長さ）を省略した場合は、開始位置から文字列の末尾までがすべて取得されます。さらに、開始位置にマイナス値を指定することで、文字列の「後ろから」数えて切り出すことも可能です。
実装/解決策
実務では、カラム内の特定のルールに基づいたデータを抽出するために利用します。例えば、先頭の数文字がカテゴリコード、残りが個別IDとなっているような場合に、substr関数でカテゴリ部分だけを切り出してGROUP BY句と組み合わせることで、カテゴリごとの集計が容易に行えます。また、マイナス値の指定を活用すれば、ファイルパスからファイル名だけを抽出するといった処理もスマートに実現できます。
サンプルプログラム
以下のコードは、テーブルの特定のカラムから部分文字列を抽出する実用的な例です。お手元のSQLite環境で実行して確認してみてください。
— テキスト確認用のテーブル作成
CREATE TABLE products (id INTEGER, serial_code TEXT);
— サンプルデータの挿入
INSERT INTO products VALUES(1, ‘JP-100-ABC’);
INSERT INTO products VALUES(2, ‘US-200-DEF’);
INSERT INTO products VALUES(3, ‘EU-300-GHI’);
— 1. 最初の2文字（国コード）を取得する
SELECT serial_code, substr(serial_code, 1, 2) AS country_code FROM products;
— 2. 後ろの3文字（商品識別子）を取得する（マイナス値を使用）
SELECT serial_code, substr(serial_code, -3) AS item_id FROM products;
— 3. 特定の区切り文字以降を取得する
— 下記は「4文字目から最後まで」を取得する例です
SELECT serial_code, substr(serial_code, 4) AS detail_code FROM products;
応用・注意点
実務で利用する際に気をつけるべき点が2つあります。
1つ目は、マルチバイト文字の扱いです。SQLiteのsubstr関数は文字単位で動作しますが、データのエンコーディングによっては意図しない結果になる場合があります。日本語などの全角文字を扱う際は、事前にテスト環境で期待通りの挙動をするか必ず確認してください。
2つ目は、開始位置の誤りです。多くのプログラミング言語（JavaやJavaScriptなど）ではインデックスが「0」から始まるため、SQLの「1」から始まる仕様と混同してしまいがちです。「SQLは1から」と意識することで、バグを未然に防ぐことができます。また、切り出す長さが対象文字列の長さを超えてもエラーにはならず、末尾までが返される仕様ですが、厳密なデータ整形が必要な場合はLENGTH関数と組み合わせてバリデーションを行うことを推奨します。
コメント