PostgreSQLの`substring`関数で文字列を抽出する方法

By /

PostgreSQLにおける文字列抽出の決定版：substring関数の徹底解説

PostgreSQLを含むリレーショナルデータベースにおいて、文字列操作は非常に基本的ながらも強力な機能の一つです。データベースに格納されたテキストデータは、多くの場合、そのままの形ではなく、特定の情報を抽出したり、フォーマットを整えたりしてから利用されます。このような文字列操作の中核をなすのが、部分文字列（サブストリング）を抽出する機能です。PostgreSQLでは、この目的のために主にsubstring関数が提供されています。

substring関数は、与えられた文字列の中から、指定された位置から特定の長さだけ、あるいは指定されたパターンに一致する部分を取り出すことができます。一見シンプルな機能に見えますが、その引数の指定方法には複数のバリエーションがあり、それぞれに異なる挙動や用途があります。また、インデックスの考え方、長さの指定方法、正規表現との組み合わせなど、理解を深めることでより高度なデータ操作が可能になります。

本記事では、PostgreSQLのsubstring関数について、その基本的な使い方から、様々な引数の指定方法、SQL標準構文、正規表現を使った抽出、さらには他の文字列関数との比較、NULL値やエラーの扱い、パフォーマンスに関する考慮事項、そして実際の応用例に至るまで、詳細かつ網羅的に解説します。約5000語というボリュームで、substring関数のすべてを理解し、自在に使いこなせるようになることを目指します。

1. substring関数とは何か

substring関数は、PostgreSQLが提供する文字列関数の一つで、ソース文字列から部分文字列を抽出するために使用されます。この関数は非常に一般的であり、SQL標準でも定義されているため、他の多くのデータベースシステムでも同様の名前と機能で提供されています。

substring関数を使う主な目的は以下の通りです。

  • 長い文字列から、特定の開始位置以降の情報を取得する。
  • 文字列の中央部分や、特定の長さの部分だけを切り出す。
  • 固定長フォーマットのデータから各フィールドを抽出する。
  • 特定の区切り文字がない場合に、位置と長さを指定してデータを分解する。
  • 正規表現パターンに一致する部分を抽出する。

substring関数は、これらのタスクを柔軟に実行できるため、データクレンジング、ETL処理、レポート生成など、様々な場面で不可欠なツールとなります。

2. substring関数の基本的な構文

PostgreSQLのsubstring関数には、主に以下の3つの基本的な構文パターンがあります。

  1. substring(string, start): ソース文字列（string）の指定された開始位置（start）から、文字列の最後までを抽出します。
  2. substring(string, start, length): ソース文字列（string）の指定された開始位置（start）から、指定された長さ（length）だけを抽出します。
  3. substring(string from pattern) または substring(string, pattern): ソース文字列（string）から、指定された正規表現パターン（pattern）に一致する部分を抽出します。

これらに加えて、SQL標準で定義されているFROM句とFOR句を使用する構文もPostgreSQLはサポートしています。

  1. substring(string from start): substring(string, start) と同じです。
  2. substring(string from start for length): substring(string, start, length) と同じです。
  3. substring(string from pattern) または substring(string from pattern for escape): substring(string, pattern) / substring(string, pattern, escape) と同じです。

これらの構文は、機能的には重複していますが、書き方の違いにより可読性や他のデータベースシステムとの互換性に影響を与えます。順番に詳細を見ていきましょう。

3. 位置と長さを指定する構文の詳細

まずは、位置（start）と長さ（length）を使って部分文字列を抽出する構文について掘り下げます。この形式がsubstring関数の最も基本的で頻繁に使われるパターンです。

3.1 substring(string, start) または substring(string from start)

この構文は、ソース文字列の指定された開始位置から、文字列の終端までのすべてを抽出します。

sql
substring(string, start)
substring(string from start)

  • string: 部分文字列を抽出したい元の文字列です。TEXT, VARCHAR, CHARなどの文字列データ型を受け入れます。
  • start: 抽出を開始する位置を指定する整数値です。PostgreSQLでは、文字列のインデックスは 1から始まります。

例1: 基本的な抽出

“`sql
SELECT substring(‘Hello PostgreSQL’, 7);
— 結果: ‘PostgreSQL’ (7文字目の’P’から最後まで)

SELECT substring(‘abcdefghij’ FROM 3);
— 結果: ‘cdefghij’ (3文字目の’c’から最後まで)
“`

start引数の詳細な挙動

start引数は整数値ですが、様々な値を取り得ます。それぞれのケースを見てみましょう。

  • startが正の値の場合:
    • startが文字列の長さ以下の正の値である場合、その位置から終端までが抽出されます。
    • startが文字列の長さよりも大きい正の値である場合、結果は空文字列（”）になります。エラーにはなりません。
      sql
      SELECT substring('abc', 1); -- 結果: 'abc'
      SELECT substring('abc', 2); -- 結果: 'bc'
      SELECT substring('abc', 3); -- 結果: 'c'
      SELECT substring('abc', 4); -- 結果: '' (文字列長は3なので、4文字目以降は存在しない)
      SELECT substring('abc', 10); -- 結果: ''
  • startが1の場合: 文字列全体が抽出されます。
    sql
    SELECT substring('abc', 1); -- 結果: 'abc'
  • startが0の場合: PostgreSQLでは、startが0の場合も1として扱われます。これはSQL標準にはないPostgreSQLの拡張あるいは挙動です。他のデータベースではエラーになる場合があります。
    sql
    SELECT substring('abc', 0); -- 結果: 'abc' (0は1として扱われる)
  • startが負の値の場合: PostgreSQLでは、startが負の値の場合も、特別な意味を持たず、そのままその位置から抽出を試みますが、通常は結果が空文字列になるか、意図しない結果になります。多くの文字列関数のインデックス指定では末尾からの相対位置を示すために負の値が使われることがありますが、substring関数のstart引数ではそうではありません。負の値は文字位置としては無効とみなされ、結果は空文字列になります。
    sql
    SELECT substring('abc', -1); -- 結果: '' (負の値は有効な開始位置とみなされない)
  • startがNULLの場合: start引数がNULLの場合、関数の結果はNULLになります。
    sql
    SELECT substring('abc', NULL); -- 結果: NULL

string引数の詳細な挙動

  • stringがNULLの場合: string引数がNULLの場合、結果はNULLになります。
    sql
    SELECT substring(NULL, 1); -- 結果: NULL
  • stringが空文字列（”）の場合: startの値に関わらず、結果は空文字列になります。
    sql
    SELECT substring('', 1); -- 結果: ''
    SELECT substring('', 10); -- 結果: ''

3.2 substring(string, start, length) または substring(string from start for length)

この構文は、ソース文字列の指定された開始位置から、指定された長さだけを抽出します。これが最も柔軟性の高い位置指定による抽出方法です。

sql
substring(string, start, length)
substring(string from start for length)

  • string: 部分文字列を抽出したい元の文字列です。
  • start: 抽出を開始する位置を指定する整数値です（1ベースインデックス）。
  • length: 抽出する文字数を指定する整数値です。

例2: 開始位置と長さを指定した抽出

“`sql
SELECT substring(‘Hello PostgreSQL’, 7, 4);
— 結果: ‘Post’ (7文字目の’P’から4文字)

SELECT substring(‘abcdefghij’ FROM 3 FOR 5);
— 結果: ‘cdefg’ (3文字目の’c’から5文字)

SELECT substring(‘ABCDEFG’, 3, 2);
— 結果: ‘CD’ (3文字目の’C’から2文字)
“`

start引数の詳細な挙動 (length指定ありの場合)

基本的な挙動はlength指定がない場合と同じですが、lengthとの組み合わせで結果が変わります。

  • startが正の値の場合:
    • start + length が文字列の長さよりも大きい場合、開始位置から終端までの残りのすべてが抽出されます。指定されたlengthよりも短くなる可能性があります。
      sql
      SELECT substring('abcdef', 3, 10); -- 結果: 'cdef' (3文字目から10文字だが、実際は終端まで)
    • startが文字列の長さよりも大きい正の値である場合、結果は空文字列になります。
      sql
      SELECT substring('abc', 4, 2); -- 結果: '' (開始位置が文字列外)
  • startが1の場合: 先頭から指定された長さだけが抽出されます。これはleft(string, length)と等価です。
    sql
    SELECT substring('abc', 1, 2); -- 結果: 'ab'
  • startが0の場合: 1として扱われます。
    sql
    SELECT substring('abc', 0, 2); -- 結果: 'ab' (0は1として扱われる)
  • startが負の値の場合: 負の値は有効な開始位置とみなされず、結果は空文字列になります。
    sql
    SELECT substring('abc', -1, 2); -- 結果: ''
  • startがNULLの場合: 結果はNULLになります。
    sql
    SELECT substring('abc', NULL, 2); -- 結果: NULL

length引数の詳細な挙動

length引数も整数値です。

  • lengthが正の値の場合: 指定された長さだけ抽出されます。ただし、開始位置から終端までの長さがlengthより短い場合は、終端までが抽出されます。
    sql
    SELECT substring('abcdef', 3, 2); -- 結果: 'cd'
    SELECT substring('abcdef', 3, 10); -- 結果: 'cdef' (終端まで)
  • lengthが0の場合: 結果は常に空文字列（”）になります。開始位置に関わらず、長さ0の部分文字列は常に空です。
    sql
    SELECT substring('abcdef', 3, 0); -- 結果: ''
    SELECT substring('abcdef', 1, 0); -- 結果: ''
    SELECT substring('abcdef', 10, 0); -- 結果: ''
  • lengthが負の値の場合: PostgreSQLでは、length引数が負の値の場合、エラーになります。
    sql
    -- SELECT substring('abcdef', 3, -2); -- エラー: negative substring length not allowed
  • lengthがNULLの場合: 結果はNULLになります。
    sql
    SELECT substring('abcdef', 3, NULL); -- 結果: NULL

string引数の詳細な挙動 (length指定ありの場合)

  • stringがNULLの場合: 結果はNULLになります。
    sql
    SELECT substring(NULL, 1, 2); -- 結果: NULL
  • stringが空文字列（”）の場合: startやlengthの値に関わらず、結果は空文字列になります。
    sql
    SELECT substring('', 1, 2); -- 結果: ''

3.3 SQL標準構文 (FROM, FOR) について

substring(string from start)およびsubstring(string from start for length)の構文は、SQL標準で定義されています。

sql
SUBSTRING(string FROM start)
SUBSTRING(string FROM start FOR length)

PostgreSQLはこれらの構文もサポートしており、機能的にはカンマ区切りの構文と全く同じです。

sql
SELECT substring('Hello PostgreSQL' FROM 7); -- 結果: 'PostgreSQL'
SELECT substring('Hello PostgreSQL' FROM 7 FOR 4); -- 結果: 'Post'

これらの構文を使用する利点は、SQL標準に準拠しているため、他のデータベースシステム（例: Oracle, SQL Serverの一部バージョン）との互換性が高い点です。特に、データベースシステム間でのSQLスクリプトの移植性を考慮する場合に有用です。PostgreSQL内での使用においては、カンマ区切り構文と機能的な違いはありませんが、一部の開発者は可読性の観点からFROM/FOR構文を好む場合があります。

4. 正規表現による抽出構文の詳細

substring関数は、位置と長さを指定するだけでなく、正規表現パターンに一致する部分を抽出することも可能です。この機能により、より柔軟で強力なパターンベースの抽出が可能になります。

正規表現を使う構文は以下の通りです。

  1. substring(string, pattern)
  2. substring(string from pattern)
  3. substring(string, pattern, escape)
  4. substring(string from pattern for escape)

substring(string, pattern)とsubstring(string from pattern)は全く同じ機能を提供します。escape引数は正規表現内でエスケープ文字を指定するために使用されますが、一般的な使用では省略されることが多いです。

sql
substring(string, pattern)
substring(string from pattern)

  • string: 部分文字列を抽出したい元の文字列。
  • pattern: 抽出したい部分文字列を定義する正規表現パターンを指定する文字列。

抽出される部分について

重要な点として、PostgreSQLのsubstring(string, pattern)（およびsubstring(string from pattern)）は、基本的に 正規表現パターン全体に一致した最初の一部分 を返します。

例えば、「数字が3つ連続する部分」を抽出したい場合、パターン\d{3}を指定します。

sql
SELECT substring('abc123xyz456', '\d{3}');
-- 結果: '123' (最初に見つかった3桁の数字)

パターンに複数のカッコ（キャプチャリンググループ）が含まれている場合でも、デフォルトではパターン全体に一致した部分が返されます。特定のキャプチャリンググループの中身だけを抽出したい場合は、regexp_matches関数など、他の正規表現関数を使用するのが一般的です。ただし、一部のSQL方言やPostgreSQLの古いバージョン、あるいは特定の状況では、カッコ内の部分が抽出されるような挙動が見られることも報告されていますが、PostgreSQLの公式ドキュメントに基づく標準的な挙動は、パターン全体の一致部分を返す というものです。混乱を避けるため、キャプチャグループを抽出したい場合はregexp_matchesを使用することを強く推奨します。

正規表現の基本（substringで使うために）

substring関数で正規表現を使うためには、基本的な正規表現の知識が必要です。ここでは、substringでよく使われる基本的な要素を簡単に紹介します。

  • メタ文字:
    • .: 任意の一文字（改行を除く）
    • *: 直前の要素が0回以上繰り返される
    • +: 直前の要素が1回以上繰り返される
    • ?: 直前の要素が0回または1回出現する
    • |: 論理和（OR）。例: abc|xyz は abc または xyz に一致
    • ^: 文字列の先頭に一致
    • $: 文字列の末尾に一致
  • 文字クラス:
    • [abc]: a, b, cのいずれか一文字に一致
    • [^abc]: a, b, c以外の任意の一文字に一致
    • [0-9]: 任意の数字一文字に一致（\d と同義）
    • [a-z]: 任意の小文字アルファベット一文字に一致
    • [A-Z]: 任意の大文字アルファベット一文字に一致
    • [a-zA-Z]: 任意のアルファベット一文字に一致
    • [a-zA-Z0-9]: 任意の英数字一文字に一致（\w と同義ではないので注意）
  • 定義済み文字クラス:
    • \d: 任意の数字（[0-9] と同義）
    • \D: 数字以外の任意の一文字
    • \w: 任意の単語構成文字（アルファベット、数字、アンダースコア）
    • \W: 単語構成文字以外の任意の一文字
    • \s: 任意の空白文字（スペース、タブ、改行など）
    • \S: 空白文字以外の任意の一文字
  • 量指定子:
    • {n}: 直前の要素がちょうど n 回繰り返される
    • {n,}: 直前の要素が n 回以上繰り返される
    • {n,m}: 直前の要素が n 回以上 m 回以下繰り返される
  • エスケープ:
    • 正規表現のメタ文字そのものをマッチさせたい場合は、バックスラッシュ（\）でエスケープします。例: ドット（.）にマッチさせたい場合は \. とします。バックスラッシュそのものにマッチさせたい場合は \\ とします。

正規表現による抽出例

例3: 特定の文字パターンを持つ部分の抽出

“`sql
— 数字の塊を抽出
SELECT substring(‘Order #1001 processed.’, ‘\d+’);
— 結果: ‘1001’

— アルファベットの連続（単語）を抽出
SELECT substring(‘This is a test.’, ‘\w+’);
— 結果: ‘This’ (最初の単語)

— メールアドレス形式の文字列からドメイン部分を抽出（簡易例）
— ユーザー名と@記号を含む部分全体に一致させてしまうことに注意
SELECT substring(‘[email protected]’, ‘\w+@\w+.\w+’);
— 結果: ‘[email protected]’ (パターン全体に一致)

— ファイル名から拡張子を抽出（ドット以降）
SELECT substring(‘document.txt’, ‘..*$’); — ドットから行末まで
— 結果: ‘.txt’

— URLからプロトコル部分を抽出
SELECT substring(‘https://www.example.com’, ‘^https?://’);
— 結果: ‘https://’
“`

escape引数について

substring(string, pattern, escape) 構文では、正規表現内でエスケープ文字として機能する文字を指定できます。デフォルトではバックスラッシュ（\）が使用されるため、通常はこの引数を省略します。例えば、正規表現パターン自体にバックスラッシュを多く含む場合に、別の文字をエスケープ文字として指定することで可読性を向上させることができます。

“`sql
— デフォルトのエスケープ文字()を使用する場合（通常はこちら）
SELECT substring(‘abc\def’, ‘\\.’); — \\ は \ というパターンを表す（SQL文字列リテラルと正規表現のエスケープ）
— 結果: ‘\d’

— エスケープ文字を’#’に変更する場合
— SELECT substring(‘abc#def’, ‘##.’, ‘#’); — この例は動作しない。PostgreSQLのsubstringのescape引数は正規表現パーサのエスケープ文字を変更するものではなく、SQL標準に由来する挙動（LIKEのESCAPE句に似ている）だが、正規表現エンジンによってはサポートされない。
— PostgreSQLのドキュメントによると、この構文は他のデータベースシステムとの互換性のために存在し、patternの解釈は通常のエスケープ規則(\を使用)に従います。escape引数が実際にパターン解釈に影響を与える具体的なケースは限定的か、あるいは古い挙動かもしれません。実用的には、正規表現のエスケープには常に\を使用し、escape引数は省略するのが安全です。
“`

実務においては、substring(string, pattern) または substring(string from pattern) 構文を使用し、正規表現内のエスケープには常にバックスラッシュ（\）を使用するのが最も一般的で推奨される方法です。

正規表現抽出におけるNULLと空文字列

  • stringがNULLの場合: 結果はNULL。
  • patternがNULLの場合: 結果はNULL。
  • escapeがNULLの場合: 結果はNULL。
  • stringが空文字列（”）の場合: パターンマッチングは行われず、結果は空文字列。
  • patternが空文字列（”）の場合: 結果は空文字列。
  • パターンが文字列内に全く一致しない場合: 結果はNULLになります（空文字列ではありません）。
    sql
    SELECT substring('abc', '\d+'); -- 結果: NULL (数字がないため)

5. substring関数と他の文字列関数の比較

PostgreSQLにはsubstring関数以外にも、文字列から部分文字列を抽出したり、文字列を操作したりするための関数が多数あります。ここでは、特に関連の深い関数と比較し、それぞれの使い分けについて考えます。

5.1 substr

substr関数は、多くのデータベースシステムでsubstring関数と同義として提供されています。PostgreSQLでも、substr(string, start) および substr(string, start, length) は、それぞれ substring(string, start) および substring(string, start, length) と全く同じ挙動をします。

sql
SELECT substr('Hello', 2, 3);
-- 結果: 'ell' (substring('Hello', 2, 3) と同じ)

どちらの関数を使うかは、個人の好みや、他のデータベースシステムとの互換性の要件によります。機能的には違いがありません。ただし、正規表現を使った抽出はsubstrではできません。substrは位置と長さを指定する基本的な抽出のみをサポートします。

5.2 left および right

left(string, count)関数は、文字列の先頭から指定された文字数（count）だけを抽出します。right(string, count)関数は、文字列の末尾から指定された文字数だけを抽出します。

sql
SELECT left('abcdef', 3); -- 結果: 'abc'
SELECT right('abcdef', 3); -- 結果: 'def'

これらの関数は、substring関数を使って表現することも可能です。

  • left(string, count) は substring(string, 1, count) と等価です。
  • right(string, count) は substring(string, greatest(1, length(string) - count + 1), count) と等価です。末尾からの抽出を正確に表現するには、開始位置を計算する必要があります。greatest(1, ...) は開始位置が1未満にならないようにするためです。

leftやrightを使う利点は、先頭または末尾からの抽出という意図がコード上で明確になることです。特定の端からの抽出が頻繁に必要な場合は、これらの関数を使う方がコードの可読性が向上します。一方、substringはより汎用的な指定が可能であり、中間部分の抽出などに適しています。

5.3 split_part

split_part(string, delimiter, field)関数は、文字列を指定された区切り文字（delimiter）で分割し、指定されたフィールド番号（field）の部分を返します。フィールド番号は1から始まります。

sql
SELECT split_part('a,b,c,d', ',', 2); -- 結果: 'b'
SELECT split_part('apple.orange.grape', '.', 3); -- 結果: 'grape'

split_partは、区切り文字によって構造化された文字列から特定の要素を取り出すのに非常に便利です。しかし、区切り文字が存在しない、あるいは区切り文字の数が一定でないような文字列から、単に位置と長さで部分を取り出したい場合にはsubstringが適しています。また、split_partは指定したフィールドが存在しない場合に空文字列を返しますが、substringは開始位置や長さによってNULLや空文字列、あるいは終端までの残りを返すなど、挙動が異なります。

例えば、ファイル名から拡張子を取り出す場合、ファイル名にドットが一つしかないという保証があればsplit_partも使えますが、複数のドットがある場合（例: archive.tar.gz）やドットがない場合も考慮すると、正規表現を使ったsubstringや他の正規表現関数の方が柔軟に対応できます。

“`sql
— ファイル名から拡張子
— split_part(‘archive.tar.gz’, ‘.’, 3) -> ‘gz’ (この場合は正しい)
— split_part(‘document.txt’, ‘.’, 2) -> ‘txt’
— split_part(‘noextension’, ‘.’, 2) -> ” (フィールド2が存在しない)

— substringを使った正規表現
— substring(‘archive.tar.gz’, ‘.[^.]$’) -> ‘.gz’ (最後のドット以降)
— substring(‘document.txt’, ‘.[^.]$’) -> ‘.txt’
— substring(‘noextension’, ‘.[^.]*$’) -> NULL (パターンに一致しない)
“`
この例からもわかるように、どのようなデータを扱っているか、どのような結果を期待するかによって適切な関数が異なります。

5.4 正規表現関数 (regexp_matches, regexp_split_to_array, regexp_replace)

PostgreSQLは、より高度な正規表現操作のためにregexp_matches, regexp_split_to_array, regexp_replaceなどの関数も提供しています。

  • regexp_matches(string, pattern [, flags]): 文字列から正規表現に一致する部分を抽出します。パターンにカッコ（キャプチャリンググループ）が含まれている場合、その各グループに一致した部分を要素とするテキストの配列を返します。複数のマッチがある場合は、デフォルトでは最初の一致のみを返しますが、'g'フラグを指定するとすべてのマッチを返すセット関数として振る舞います。特定のグループを抽出したい場合にsubstring(string, pattern)の代替として使用されます。

    “`sql
    — ユーザー名とドメインを別々に抽出
    SELECT regexp_matches(‘[email protected]’, ‘(\w+)@(\w+.\w+)’);
    — 結果: {“testuser”,”example.com”} (text配列)

    — substringではパターン全体が返される
    SELECT substring(‘[email protected]’, ‘(\w+)@(\w+.\w+)’);
    — 結果: ‘[email protected]’
    “`

  • regexp_split_to_array(string, pattern [, flags]): 文字列を正規表現パターンで指定された区切り文字で分割し、テキストの配列として返します。split_partよりも柔軟な区切り文字を指定できます。

    sql
    -- 複数の区切り文字で分割（カンマまたはセミコロン）
    SELECT regexp_split_to_array('apple,banana;cherry', '[,;]');
    -- 結果: {"apple","banana","cherry"}

  • regexp_replace(string, pattern, replacement [, flags]): 文字列中で正規表現パターンに一致する部分を別の文字列（replacement）で置換します。

    sql
    -- 数字を全てXに置換
    SELECT regexp_replace('Order #12345', '\d+', 'X');
    -- 結果: 'Order #X'

substringの正規表現構文は、パターン全体の一致を単純に抽出する場合に便利です。より複雑な正規表現を使って、複数の要素を抽出したり、文字列を分割したり、置換したりする場合は、これらの専用の正規表現関数を使うべきです。substringは正規表現による抽出の入り口としてはシンプルですが、正規表現の真の力を引き出すには他の関数が必要になります。

5.5 overlay

overlay(string placing new_substring from start [for length])関数は、文字列の特定の部分を別の文字列で置き換えます。

“`sql
SELECT overlay(‘abcdef’ placing ‘XYZ’ from 2 for 3);
— 結果: ‘aXYZef’ (‘b’, ‘c’, ‘d’を’XYZ’で置き換え)

SELECT overlay(‘abcdef’ placing ‘XYZ’ from 2); — 長さ指定なしの場合は、開始位置から最後までを置き換え
— 結果: ‘aXYZ’
“`

これは部分文字列の抽出ではなく置換を行う関数ですが、内部的には指定された範囲の部分文字列を特定するという処理を含んでいます。特定の文字列の特定の部分を編集したい場合に利用します。

6. NULL値とエラーハンドリング

PostgreSQLの関数は、引数にNULL値が含まれる場合に結果がNULLになるという基本的なルール（「NULL伝播」）に従うことが一般的です。substring関数もこのルールに概ね従いますが、無効な引数値の場合はエラーになるケースもあります。

  • ソース文字列 (string) がNULL: substring(NULL, ...) は結果として NULL を返します。
  • 開始位置 (start) がNULL: substring(..., NULL, ...) は結果として NULL を返します。
  • 長さ (length) がNULL: substring(..., ..., NULL) は結果として NULL を返します。
  • 正規表現パターン (pattern) がNULL: substring(..., NULL) は結果として NULL を返します。
  • 正規表現エスケープ文字 (escape) がNULL: substring(..., ..., NULL) は結果として NULL を返します。

sql
SELECT substring('abc', NULL, 2); -- 結果: NULL
SELECT substring('abc', 1, NULL); -- 結果: NULL
SELECT substring(NULL, 1, 2); -- 結果: NULL
SELECT substring('abc', NULL); -- 結果: NULL
SELECT substring('abc', NULL); -- 結果: NULL (正規表現パターンがNULL)

無効な引数値によるエラー

length引数に負の値を指定した場合はエラーになります。

sql
-- SELECT substring('abc', 1, -2); -- エラー: negative substring length not allowed

start引数が負の値の場合はエラーにはならず、空文字列を返します。

正規表現パターンが無効な場合、クエリ実行時にエラーが発生します。

sql
-- SELECT substring('abc', '['); -- エラー: invalid regular expression: brackets [...] not balanced

エラーハンドリングとNULLの扱い

実際のリレーショナルデータベースのデータは、NULL値を含んでいたり、予期しないフォーマットの文字列が含まれていたりすることがよくあります。substring関数を使う際には、これらの可能性を考慮する必要があります。

  • NULL値の回避: substring関数の結果がNULLになることを避けたい場合（例: NULLを空文字列として扱いたい）、COALESCE関数を使うことができます。
    sql
    SELECT COALESCE(substring('abc', 10), ''); -- 結果: '' (開始位置が文字列外でNULLになる代わりに空文字列)
    SELECT COALESCE(substring(NULL, 1), ''); -- 結果: '' (入力がNULLでNULLになる代わりに空文字列)
  • 無効なデータへの対処: 想定外のデータによってsubstring関数がエラーを起こす可能性がある場合（例: 不正な正規表現パターンがデータとして格納されている）、CASE式や、PL/pgSQLなどの手続き言語における例外ハンドリングを使用することを検討できます。また、データを事前に検証またはクリーニングすることも重要です。

7. パフォーマンスに関する考慮事項

substring関数は比較的軽量な文字列操作ですが、大量のデータに対して実行する場合や、複雑な正規表現を使用する場合はパフォーマンスに影響を与える可能性があります。

  • 位置/長さ指定 vs 正規表現: 位置と長さによる抽出は、正規表現による抽出よりも一般的に高速です。正規表現エンジンは、パターンの解釈とマッチングに計算コストがかかります。単純な位置指定で済む場合は、そちらを選択すべきです。
  • 大きな文字列: 非常に長い文字列から部分文字列を抽出する場合、文字列全体をメモリに読み込む必要があるため、メモリ使用量や処理時間に影響を与える可能性があります。
  • インデックス: 通常、テーブルの文字列カラムに対するインデックスは、substring関数を使った抽出条件（例: WHERE substring(column, ...) = '...'）の最適化には直接的に寄与しません。なぜなら、関数はインデックス可能なカラムの値を変換してしまうからです。
    • ただし、抽出した部分文字列に対する検索が頻繁に行われる場合は、そのsubstring式の計算結果に対して関数インデックスを作成することが有効な場合があります。例えば、メールアドレスのドメイン部分で検索することが多い場合、CREATE INDEX idx_email_domain ON users (substring(email FROM '@(.*)$')); のようなインデックスが役立つ可能性があります（正規表現関数インデックスのサポート状況や構文はPostgreSQLのバージョンによります）。
    • また、LIKEや正規表現を使ったパターンマッチングは、特定の条件下でGiSTやGINインデックス（text_pattern_opsやpg_trgm拡張など）を利用できることがありますが、これはsubstring関数自体によるインデックス化とは異なります。
  • 大量の行: 大量の行に対してsubstring関数を適用すると、その計算コストが累積されます。可能であれば、データをロードする前や、アプリケーション側で文字列処理を行うなど、データベースのクエリ処理負荷を軽減する手段も検討します。

パフォーマンスがクリティカルな場合は、実行計画（EXPLAIN）を確認し、必要に応じて関数インデックスの利用やクエリの再構成を検討することが重要です。

8. 実際の応用例

substring関数は、様々なデータ操作タスクで利用できます。いくつかの具体的な応用例を見てみましょう。

応用例1: 固定長フィールドデータのパース

ログファイルや古いデータ形式など、固定長で情報が格納されている文字列からデータを抽出する。

例えば、「YYYYMMDDhhmmss コード 値」のようなフォーマットでデータが格納されているカラムがあるとします。

“`sql
CREATE TABLE fixed_data (
raw_string TEXT
);

INSERT INTO fixed_data VALUES
(‘20231027103000ABC 123’),
(‘20231027103500DEF 456’),
(‘20231028090000GHI 789’);

SELECT
substring(raw_string, 1, 8) AS date_part,
substring(raw_string, 9, 6) AS time_part,
substring(raw_string, 15, 3) AS code_part,
substring(raw_string, 18, 5) AS value_part
FROM fixed_data;

— 結果例:
— date_part | time_part | code_part | value_part
— ———-|———–|———–|———–
— 20231027 | 103000 | ABC | 123
— 20231027 | 103500 | DEF | 456
— 20231028 | 090000 | GHI | 789
``
このように、substring`関数は開始位置と長さを正確に指定することで、固定長データの各要素を容易に分解できます。

応用例2: データクリーニング – 不要なプレフィックス/サフィックスの除去

特定のプレフィックスやサフィックスを持つ文字列から、それらを取り除いた本体部分を抽出する。

“`sql
CREATE TABLE products (
product_code VARCHAR(50)
);

INSERT INTO products VALUES
(‘CODE-A123’),
(‘CODE-B456’),
(‘ITEM-X999’),
(‘A-789’); — プレフィックスが異なるデータも混在

— ‘CODE-‘ プレフィックスを除去
SELECT
product_code,
CASE
WHEN product_code LIKE ‘CODE-%’
THEN substring(product_code, 6) — ‘CODE-‘ は5文字なので、6文字目から最後まで
ELSE product_code — それ以外のデータはそのまま
END AS cleaned_code
FROM products;

— 結果例:
— product_code | cleaned_code
— ————-|————-
— CODE-A123 | A123
— CODE-B456 | B456
— ITEM-X999 | ITEM-X999
— A-789 | A-789
``
この例では、LIKE演算子でパターンをチェックし、一致する場合のみsubstringでプレフィックスを取り除いています。CASE`式を組み合わせることで、条件に応じたデータ処理が可能です。

応用例3: 文字列のフォーマット変換

電話番号のように、特定のフォーマットで格納されている文字列から、ハイフンなどを取り除いて数字部分だけを抽出する。あるいは、数字列にハイフンを挿入して表示用のフォーマットに変換する（この場合はsubstringと他の文字列関数や連結を組み合わせます）。

“`sql
CREATE TABLE contacts (
phone_number VARCHAR(20)
);

INSERT INTO contacts VALUES
(‘090-1234-5678′),
(’03-9876-5432’),
(‘0120-000-111’);

— ハイフンを取り除いて数字部分だけを抽出（正規表現を使用）
SELECT
phone_number,
regexp_replace(phone_number, ‘[^0-9]’, ”, ‘g’) AS digits_only — 数字以外の文字を空文字列に置換
FROM contacts;

— 結果例:
— phone_number | digits_only
— ————-|————
— 090-1234-5678 | 09012345678
— 03-9876-5432 | 0398765432
— 0120-000-111 | 0120000111
``
数字以外の文字を取り除くにはregexp_replaceが便利ですが、もしハイフンの位置が固定であれば、substring`で各部分を抽出して連結することも可能です。例えば、「XXX-YYYY-ZZZZ」形式から数字だけを抽出する場合：

sql
-- 固定フォーマットの電話番号から数字部分を抽出（substringとconcatを使用）
SELECT
phone_number,
substring(phone_number, 1, 3) || substring(phone_number, 5, 4) || substring(phone_number, 10, 4) AS digits_only -- 1-3文字目, 5-8文字目, 10-13文字目を連結
FROM contacts WHERE phone_number LIKE '___-____-____'; -- 例として特定のフォーマットに絞る
ただし、この方法はフォーマットが厳密に固定されている場合にのみ有効です。フォーマットが変動する場合は正規表現が強力です。

応用例4: URLからの情報抽出

URL文字列から特定の要素（ホスト名、パス、クエリストリングなど）を抽出する。これは複雑な正規表現とsubstringまたはregexp_matchesを組み合わせる代表的な例です。

“`sql
CREATE TABLE web_logs (
request_url TEXT
);

INSERT INTO web_logs VALUES
(‘https://www.example.com/path/to/page?id=123&lang=en’),
(‘http://sub.domain.co.jp/index.html’),
(‘/local/resource’); — 相対パスの場合も考慮

— ホスト名を抽出（プロトコルとパスの間の部分）
SELECT
request_url,
substring(request_url FROM ‘://([^/]+)’) AS host_name — ‘://’ の後に続くスラッシュ以外の文字の塊をキャプチャ
FROM web_logs;

— 結果例:
— request_url | host_name
— ————————————————|———-
— https://www.example.com/path/to/page?id=123&lang=en | www.example.com
— http://sub.domain.co.jp/index.html | sub.domain.co.jp
— /local/resource | NULL (パターンに一致しない)
``
上記の例では、substring(string FROM pattern)構文を使用し、pattern内でカッコ()を使っています。しかし、前述のように、PostgreSQLのsubstring関数はデフォルトではカッコ内のグループではなく、パターン全体に一致した部分を返すため、この例は意図したホスト名を返すのではなく、://www.example.comのような文字列を返す可能性があります（実際に試すとPostgreSQL 13以降ではカッコ内の第一グループを返すようです。ドキュメントの記載と挙動に乖離がある可能性があり、注意が必要です。安全策としてはregexp_matches`を使用します）。

より安全な正規表現による特定グループの抽出例 (regexp_matchesを使用)

“`sql
— ホスト名を抽出 (regexp_matchesを使用)
SELECT
request_url,
(regexp_matches(request_url, ‘://([^/]+)’))[1] AS host_name — regexp_matchesの結果配列の1番目の要素
FROM web_logs;

— 結果例 (regexp_matchesの場合):
— request_url | host_name
— ————————————————|———-
— https://www.example.com/path/to/page?id=123&lang=en | www.example.com
— http://sub.domain.co.jp/index.html | sub.domain.co.jp
— /local/resource | NULL
``
正規表現で特定のキャプチャリンググループを抽出したい場合は、regexp_matches関数を使用し、その結果として得られる配列から要素を取り出すのが、PostgreSQLにおける推奨される方法です。ただし、substring関数の正規表現構文でカッコがどのように扱われるかはPostgreSQLのバージョンによって微妙な違いがあったり、ドキュメントの記述が追いついていない場合もあるため、利用しているPostgreSQLのバージョンで実際の挙動を確認することが重要です。一般的には、substring(string, pattern)はパターン全体の一致、regexp_matches(string, pattern)` はキャプチャグループの一致、と理解しておくと良いでしょう。

9. まとめ

PostgreSQLのsubstring関数は、文字列から部分文字列を抽出するための非常に強力で柔軟なツールです。

  • 位置と長さによる抽出: substring(string, start, length) の形式は、文字列内の任意の位置から特定の長さの部分を正確に切り出すのに適しています。start引数は1ベースインデックスであり、length引数は抽出する文字数です。lengthを省略した場合は、開始位置から最後までが抽出されます。SQL標準構文としてFROM/FOR句を使用することも可能です。
  • 正規表現による抽出: substring(string, pattern) または substring(string from pattern) の形式は、複雑なパターンに一致する部分を抽出するのに役立ちます。ただし、この構文は基本的にパターン全体の一致部分を返すため、正規表現のキャプチャリンググループを使って特定の要素を抽出したい場合は、regexp_matches関数のような他の正規表現関数を使う方が一般的で明確です。
  • 他の関数との比較: substrは位置と長さ指定の同義関数、leftやrightは端からの抽出に特化、split_partは区切り文字による分割に適しています。正規表現によるより高度な操作（グループ抽出、分割、置換）にはregexp_matches、regexp_split_to_array、regexp_replaceが適しています。どの関数を選択するかは、抽出したいデータの性質と目的によって判断すべきです。
  • NULL値とエラー: いずれかの主要な引数（string, start, length, pattern）がNULLの場合、結果はNULLになります。lengthに負の値を指定するとエラーが発生します。適切なNULLハンドリングやデータ検証が必要です。
  • パフォーマンス: 位置指定による抽出は通常高速ですが、複雑な正規表現や非常に長い文字列に対する操作、あるいは大量の行への適用はパフォーマンスに影響を与える可能性があります。必要に応じて関数インデックスの検討やクエリの最適化を行います。

substring関数は、SQLによるデータ操作において頻繁に利用される関数の一つです。その様々な構文、引数の詳細な挙動、そして他の関連関数との使い分けを理解することで、PostgreSQLでの文字列処理能力を大幅に向上させることができます。

10. さらに学ぶために

substring関数やその他の文字列関数についてさらに詳しく学ぶには、PostgreSQLの公式ドキュメントを参照するのが最も確実です。

このドキュメントには、ここで紹介した関数以外にも、文字列の長さ、連結、置換、大文字/小文字変換など、多くの文字列操作関数が網羅されています。正規表現についても、より詳細な説明が提供されています。

実際のデータを使って様々な例を試すことも、理解を深める上で非常に有効です。ぜひご自身の環境でサンプルデータを作成し、本記事で紹介した例や、ご自身の業務で必要となる文字列処理を試してみてください。

PostgreSQLのsubstring関数は、あなたのデータ処理ツールキットにおいて間違いなく重要な位置を占めるでしょう。この記事が、その機能を最大限に活用するための手助けとなれば幸いです。

記事はここまでです。約5000語という要件を目指し、substring関数の様々な側面、特に引数の詳細な挙動、SQL標準構文、正規表現による抽出（とその限界）、他の関連関数との比較、NULL/エラー処理、パフォーマンス、応用例に焦点を当てて記述しました。正規表現による抽出の部分では、PostgreSQLのsubstringとregexp_matchesの使い分けについても言及し、実際のデータ操作で誤解しやすい点をクリアにするよう努めました。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール