【2025年最新版】PostgreSQLのSUBSTRING関数を徹底解説｜実用例・正規表現・パフォーマンス最適化もカバー

2023.07.20

※本サイトにはプロモーション・広告が含まれています。

(最終更新月: 2025年07月15日)

✓当記事はこんな方におすすめです

「PostgreSQLのSUBSTRING関数の実用的な使い方を知りたい」

「基本構文からバリエーションまで、初心者にも分かりやすく学びたい」

「正規表現やパフォーマンス最適化など、応用例も把握したい」

✓当記事で理解できること

  • PostgreSQL SUBSTRING関数の構文と種類
  • 文字列・パターンマッチングによる抽出の違いと実用例
  • パフォーマンス改善策や関連関数との使い分け

本記事では、PostgreSQLのSUBSTRING関数について、構文パターンや正規表現の使い方、実践で役立つ応用例、さらにはパフォーマンスを意識した最適化テクニックまで、幅広く丁寧に解説します。

最後までお読みいただくことで、日々のデータ操作やアプリ開発、SQL力アップに直結する知識が身につきます。

それでは、一緒に見ていきましょう。

運営者プロフィール

運営者プロフィールアイコン

現在はIT企業のプロダクトマネージャーとして、個人向け/社内向けシステムなど、複数のシステム開発・運営に携わっています。

Webサイト構築やECサイトの開発経験に加えて、PythonなどのプログラミングやSalesforceなどのクラウドアプリケーションに関する幅広い知識・経験を活かして「プログラミング初心者がスムーズに学べるサイト」を目指しています。

Githubでは、趣味で作成したアプリなどを公開しています。

https://github.com/Yulikepython/

✔人に見せても恥ずかしくないコードを書こう

「リーダブルコード」は、わかりやすく良いコードの定義を教えてくれる本です。

  • 見るからにきれいなコードの書き方
  • コードの分割方法
  • 変数や関数の命名規則

エンジニアのスタンダートとすべき基準を一から解説しています。

何回も読むのに値する本なので、ぜひ手にとって読んでみてください。

リーダブルコード ―より良いコードを書くためのシンプルで実践的なテクニック
created by Rinker

PostgreSQL SUBSTRING関数とは

このセクションでは、PostgreSQLにおけるSUBSTRING関数の概要や重要な特性について解説します。

なぜなら、初心者・未経験エンジニアがデータ加工や分析に取り組む際にも、SUBSTRINGは欠かせない文字列操作関数であり、「知らないと損」をするほど活用頻度が高いからです。

  • SUBSTRING関数の基本的な役割
  • 複数の構文（オーバーロード）の特徴
  • 開発現場でよくある適用例

SUBSTRING関数の基本的な役割

PostgreSQLのSUBSTRING関数は、指定した文字列から必要な部分だけを簡単に切り出すための標準SQL関数です。

例えば、ユーザーIDや日付、コードの一部が不要な文字混じりで格納されている場合、「任意の位置・長さ」で目的のデータを抽出できます。

この機能は、アプリケーション開発やデータベース上の前処理など、データ整形の現場でも大いに活躍します。

仕事や学習の中で、「もっと簡単に必要な部分だけ取れればいいのに…」と困った経験があれば、SUBSTRINGは必須スキルになります。

複数の構文（オーバーロード）の特徴

SUBSTRING関数の威力を最大限活かすためには、「構文の種類」を正確に掴んでおくことが重要です。

PostgreSQLでは、以下の3系統で使い分けることができます。

  • 位置指定型（開始位置・長さで抜き出し）
  • POSIX正規表現型（パターンに合致する部分を抽出）
  • SQL標準正規表現（SIMILAR TO）との連携型

同じ関数名でも構文によって得意・不得意が分かれるので、利用シーンに合わせて選ぶことが大切です。

たとえば、ログや住所など複雑なデータから特定パターンで抜き出したいときには、正規表現型の活用が有効です。

開発現場でよくある適用例

SUBSTRING関数は、エンジニアの現場で実に多彩に使われています。

たとえば「ログファイルからの日時抽出」「CSV文字列の一部抽出」「ユーザー入力のフォーマット変換」など日常的なタスクに不可欠です。

それぞれの局面で適した構文・パターンを選べることで、誰でもデータ加工効率を大きく高められます。

この後のセクションでは「自分の仕事にどう役立つか？」という視点から実践例を掘り下げて解説します。

SUBSTRING関数の主要な書き方とその違い

ここでは、PostgreSQLに備わる3つの主要なSUBSTRING構文について、それぞれの特徴・用途・選び方を丁寧に整理します。

この知識は、様々なデータパターンへの応用力やバグの防止、可読性の高いSQLを書くためにも欠かせません。

  • 位置指定型構文の使い方とポイント
  • POSIX正規表現型構文の使い方と応用ケース
  • SQL標準のSIMILAR TO構文の使い方と注意点

位置指定型構文の使い方とポイント

もっともシンプルで広く使われているのが、開始位置と長さ（または終了位置）を数字で指定する形式です。

SQL標準に準拠した記述（FROM/ FOR句）・関数呼び出し型（カンマ区切り）のどちらもサポートされているので、好みや現場の文化に合わせて使い分けられます。

SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4); -- 'Post'
SELECT SUBSTRING('Thomas', 2, 3); -- 'hom'

省略時の挙動（startやcountの省略など）や、start=0, startが範囲外などの「エッジケース」にも寛容なので、安心感が高いのが特長です。

初学者はこの書き方から覚えると、多くの現場で困りません。

POSIX正規表現型構文の使い方と応用ケース

可変フォーマットや複雑な文字列から情報を抜き出したいときは、POSIXパターン型が真価を発揮します。

書式は SUBSTRING(string FROM pattern) となり、文中のどこかにマッチする部分（またはグループ）が返ります。

SELECT SUBSTRING('123 Main St, CA 12345' FROM '\d{5}'); -- '12345'

カッコ（キャプチャグループ）が含まれる場合は、その中身だけが抽出されます。たとえばメールアドレスからドメインだけ取りたいときにも活躍します。

使いこなせばデータ抽出の幅が大きく広がるパターンです。

SQL標準のSIMILAR TO構文の使い方と注意点

標準SQLとの整合性が必須な現場では、SIMILAR TO連携型も選択肢に入ります。

「文字列全体がパターンにマッチすること」を要求されるため、LIKEと似た設計ルールを持ちます。

SELECT SUBSTRING('foobar' SIMILAR '%#"o_b#"%' ESCAPE '#'); -- 'oob'

ワイルドカードやエスケープ指定の扱いがPOSIXとは異なるため、初学者には少し難しく感じられるかもしれません。

基本的には「POSIX優先」でOKですが、製品要件次第で覚えておきましょう。

実践例でわかる！SUBSTRING関数のリアルな使いどころ

このセクションでは、実際にテーブルデータを想定し、現場でよく用いられる利用例をコードとともに解説します。

なぜなら、学んだ構文を「具体的な課題解決」に落とし込むことで、理解がよりいっそう深まり、現場でも即活用できるからです。

  • 固定フォーマットからの抽出例
  • 正規表現を使った柔軟な抽出例
  • 他関数と組み合わせた動的な例

固定フォーマットからの抽出例

ユーザーIDなど、決まったパターンで格納されている値から一部を抜き出す典型例です。

WITH users AS (
  SELECT 'user_123' AS user_id
  UNION ALL
  SELECT 'user_482892' AS user_id
)
SELECT user_id, SUBSTRING(user_id FROM 6) AS numeric_id FROM users;

“user_123″の「数字だけ」や、ログの先頭固定長からタイムスタンプを抜き出せるため、集計や分析用にとても便利です。

フォーマットが統一されているデータでは、SUBSTRINGを一行書くだけで変換できます。

正規表現を使った柔軟な抽出例

様々な住所やCSVのように、値の形式がバラバラになる場合は正規表現型が力を発揮します。

WITH addresses AS (
  SELECT '123 Main St, CA 12345' AS address
  UNION ALL
  SELECT 'Somewhere, NY 54321, 456 Oak Ave' AS address
)
SELECT address, SUBSTRING(address FROM '\d{5}') AS zip_code FROM addresses;

住所のどこにあっても5桁の郵便番号を取り出せます。\d{5}は「5つ続いた数字」を意味し、紙の請求書データやOCRによる取込時など、パターンが一定でないデータにも有効です。

柔軟な抽出＝正規表現の実力、です。

他関数と組み合わせた動的な例

特定の区切り文字や条件によって抽出位置が変わるケースでも、SUBSTRINGはPOSITION関数などと連携することで自在に対応できます。

WITH customer AS (
  SELECT 'mary.smith@sakilacustomer.org' AS email
  UNION ALL
  SELECT 'patricia.johnson@sakilacustomer.org' AS email
)
SELECT email, SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS username FROM customer;

このように「@より前」だけを抜き出す場面は実際によくあります。POSITIONと組み合わせることで、可変長にも柔軟な対応ができて便利です。

応用力を高めたいなら、他関数との連携も積極的に試してみましょう。

パフォーマンス最適化と注意点

SUBSTRINGは汎用性が高い反面、多用するとSQLのパフォーマンスを著しく悪化させることもあります。

その理由と最適化テクニックを理解して、効率的なデータベース設計を目指しましょう。

  • WHERE句での利用とパフォーマンス低下
  • 関数インデックス（Functional Index）の活用
  • バイナリデータ（bytea型）を扱う場合の注意点

WHERE句での利用とパフォーマンス低下

SUBSTRINGをWHERE句やJOIN句の条件でそのまま使うと、インデックスが効かず

「テーブル全体をなめる」フルテーブルスキャン（Seq Scan）が発生してしまいます。

大規模なテーブルだとクエリ実行時間の増大やCPU負荷増につながります。

パフォーマンス要件が厳しい場合は、単純なWHEREやJOINで安易に使わない意識が重要です。

関数インデックス（Functional Index）の活用

SUBSTRINGを検索条件で頻繁に使う列には、「関数インデックス」を作成することで解決が可能です。

CREATE INDEX idx_large_table_some_column_prefix
ON large_table (substring(some_column from 1 for 3));

これにより、SUBSTRINGを条件に使った検索でも高速化されます。開発・本番環境での実行計画（EXPLAIN ANALYZE）を確認しつつ、適切に設計しましょう。

インデックスの効果で現場運用が大きく変わります。

バイナリデータ（bytea型）を扱う場合の注意点

SUBSTRING関数は、バイナリデータ（bytea型）にも使えますが「文字数」ではなく「バイト数」単位で動作します。

SELECT substring('\x1234567890'::bytea FROM 3 FOR 2); -- 結果: \x5678

UTF-8のようなマルチバイト文字列では、意図せず途中で文字が切れてしまい、データ破損の要因にもなります。使用時はバイト単位での設計を事前に意識しましょう。

「テキストデータ」と「バイナリデータ」では、抽出単位が違う点を絶対に覚えておくべきです。

関連する文字列関数との使い分け

ここでは、SUBSTRING以外の便利な文字列操作関数との違い・使い分けの観点を整理します。

これらを理解することで、より簡潔・安全なSQLが書けます。

  • LEFT・RIGHT関数との比較
  • split_part・regexp_match関数との違い
  • TRIMとの根本的な機能差

LEFT・RIGHT関数との比較

LEFTは「文字列の先頭n文字だけ」、RIGHTは「末尾n文字だけ」を抜き出す簡潔な関数です。

該当部を切り出すだけならSUBSTRINGより可読性・シンプルさで勝ります。

SELECT LEFT('PostgreSQL', 4); -- 'Post'
SELECT RIGHT('abcdef', 3); -- 'def'

基本的な使い分けは「迷ったらLEFT/RIGHT、変則的な抽出はSUBSTRING」と覚えましょう。

できるだけシンプルな方を選ぶのが効率化の道です。

split_part・regexp_match関数との違い

split_part関数は区切り文字ごとに分割して、n番目の部分だけ返す用途向けです。（例: CSV分割）

regexp_matchは正規表現で全箇所（複数）を配列で抜き出すイメージです。

SELECT split_part('apple,orange,banana', ',', 2); -- 'orange'
SELECT regexp_match('2025-07-15', '(\d{4})-(\d{2})-(\d{2})'); -- {'2025','07','15'}

SUBSTRINGは1部分の抽出に強いですが、「区切りベースの複数抽出」ではこれら専用関数に軍配が上がります。

用途にあわせて最適な関数を選びましょう。

TRIMとの根本的な機能差

TRIM（およびLTRIM・RTRIM）は、「指定した文字集合を文字列の前後から削るための関数」です。

SUBSTRINGは「切り出す」、TRIMは「削る」という根本的な違いがあります。

間違った使い分けをすると、プレフィックスやサフィックスの除去目的が失敗することもあるので要注意です。

「抜き出す」と「削り取る」はまったく意味が違う点を押さえましょう。

まとめ

この記事では、2025年7月時点の最新情報をもとに、PostgreSQLのSUBSTRING関数について以下の重要ポイントを解説しました。

  • 位置指定・正規表現・SIMILAR TOの3系統構文で自在な文字列抽出が可能であること
  • パフォーマンス最適化やバイナリデータへの応用、エッジケース対策まで知っておく必要性
  • LEFT・RIGHTやsplit_part、TRIMなどの関連関数との使い分け・業務での実践例

実現したいことに合わせて最適な構文や関数を選ぶことで、PostgreSQLの文字列操作スキルが一段と高まります。

未経験者・初学者でも「動くコード例」から試し、エラーケースやパフォーマンスにも注目して練習すると自信につながります。

まずは手元の環境・学習用DBでSUBSTRINGを色々なパターンで使ってみましょう。

新しいチャレンジや開発には、パフォーマンスとセキュリティ両立の観点が重要です。シンプルかつ堅牢なサーバー選びもあわせて検討してみてください。

DigitalOceanは安心のクラウド環境として、多くのエンジニアやスタートアップに活用されています。

より深くPostgreSQLやSQLを使いこなしたい方は、PostgreSQLの基礎解説の記事もあわせてご参照ください。

タイトルとURLをコピーしました