【PostgreSQL】SUBSTRING関数の使い方

（最終更新日：2023年7月）

✔このような方へ向けて書かれた記事となります

「PostgreSQLのsubstring関数って何ができるのか？」
「substring関数の使い方が知りたい」
「substring関数の実例が見たい」

✔当記事を通じてお伝えすること

PostgreSQLのsubstring関数の基本
substring関数の書き方やその応用
substring関数の実例

当記事では、PostgreSQLのsubstring関数の基本だけでなく、そのオプションを活用した使い方まで、具体例を用いて詳しく解説しています。

ぜひ最後までご覧ください。

筆者プロフィール

【現職】プロダクトマネージャー

【副業】ブログ（月間20万PV）/YouTube/Web・アプリ制作

「プログラミング × ライティング × 営業」の経験を活かし、30後半からのIT系職へシフト。現在はプロダクトマネージャーとして、さまざまな関係者の間に入り奮闘してます。当サイトでは、実際に手を動かせるWebアプリの開発を通じて、プログラミングはもちろん、IT職に必要な情報を提供していきます。

【当ブログで紹介しているサイト】

当サイトチュートリアルで作成したデモ版日報アプリ

Django × Reactで開発したツール系Webアプリ

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

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

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

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

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

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

created by Rinker

PostgreSQLのsubstring関数について

ここでは、”PostgreSQL”とその”substring関数”についてお伝えしていきます。

PostgreSQLについて知ることで、データベース操作の理解が深まり、substring関数を理解することで文字列操作の技術が向上します。

PostgreSQLとは
substring関数の重要性

PostgreSQLとは

PostgreSQLは、オープンソースのリレーショナルデータベース管理システム（RDBMS）のひとつです。

非常に高度な機能を持ち、SQL標準に準拠しているため、多くの大規模なWebアプリケーションで採用されています。

特に、ACID（Atomicity, Consistency, Isolation, Durability）というトランザクション特性を満たし、高い信頼性を保証しています。

substring関数の重要性

substring関数は、PostgreSQLで文字列から部分文字列を取り出すための関数のこと。

特定の文字列を検索、抽出、または操作する必要がある場合、このsubstring関数は非常に有用です。

データベースの各レコードに対して特定の文字列を抽出したり、変更したりするためには、このsubstring関数を理解することが重要となります。

多くのSQL言語で使用できる方法で、以下の記事でも詳しく解説しています。

substring関数の基本操作

ここでは、「substring関数」の基本的な操作についてお伝えしていきます。

substring関数の基本操作を理解することで、データから部分文字列を取り出すスキルが向上します。

substring関数の定義とシンタックス
基本的なsubstringの使い方
応用例：サンプルコードと解説

substring関数の定義とシンタックス

substring関数は、指定した文字列から部分文字列を取り出します。

その構文は、以下のとおりです。

SUBSTRING(string FROM start FOR length)

stringは対象となる文字列、startは部分文字列の開始位置、lengthは部分文字列の長さを示します。

開始位置は1が最初です。

基本的なsubstringの使い方

基本的なsubstring関数の使い方は以下のようなものです。

SELECT SUBSTRING('Hello, World!' FROM 1 FOR 5);

このコードは、’Hello, World!’という文字列から開始位置1から5文字分を抽出するものです。

結果は’Hello’となります。

FROM句で抽出する開始位置を、FOR句で抽出する文字数を、指定するのです。

なお、SUBSTRING関数はデータベースの種類によって若干の違いがある場合がありますので、使用するデータベースに合わせた書式を確認してください。

応用例：サンプルコードと解説

substring関数は非常に強力なツールで、複雑な文字列操作にも対応しています。

以下は、一部を数字で構成された文字列から、数字部分のみ抽出する一例です。

SELECT SUBSTRING('Order1234' FROM '\d+') AS ExtractedString;

ここでは、’\d+’という正規表現を使用。

‘\d’は任意の数字を表し、’+’は1回以上の繰り返しを表します。

結果として’1234’が返されます。

ほかの文字列操作関数との比較

ここでは、「substring関数」を、ほかの文字列操作関数と比較していきます。

これらを理解することで、より多様なシチュエーションで文字列操作をおこなえるでしょう。

left関数で文字列の左側を切り取る
right関数で文字列の右側を切り取る
文字列操作関数の使い分け

left関数で文字列の左側を切り取る

left関数を使用すると、文字列の左側から指定した文字数分だけを取得できます。

以下が例です。

SELECT LEFT('Hello, World!', 5);

このクエリは’Hello, World!’という文字列から左側の5文字を取得します。

結果として’Hello’が返されます。

right関数で文字列の右側を切り取る

right関数を使用すると、文字列の右側から指定した文字数分だけを取得できます。

以下をご覧ください。

SELECT RIGHT('Hello, World!', 6);

このクエリは’Hello, World!’という文字列から右側の6文字を取得します。

結果として’World!’が返されます。

文字列操作関数の使い分け

これらの関数を使い分けることで、さまざまなシチュエーションに対応できます。

substring関数は部分文字列の抽出が可能で、特に正規表現と組み合わせることで複雑な文字列操作が可能です。

それに対して、left関数やright関数はよりシンプルな文字列操作に向いており、文字列の始まりや終わりから特定の文字数を取り出すといったシチュエーションに適しています。

文字列を取り出す様々な方法

ここでは、「substring関数」を用いて、文字列を取り出す様々な方法についてお伝えします。

これらのテクニックを理解することで、任意の部分文字列の抽出が可能です。

指定した文字数だけ取り出す
文字列の途中から最後までを取得する
文字列を後ろから取得する
文字列を前から取得する

指定した文字数だけ取り出す

substring関数を用いて、指定した文字数だけを取り出すことが可能です。

以下に例を示します。

SELECT SUBSTRING('Hello, World!' FROM 1 FOR 5);

このクエリは、’Hello, World!’という文字列から開始位置1から5文字分を抽出するもので、結果として’Hello’が返されます。

文字列の途中から最後までを取得する

substring関数は、文字列の途中から最後までを取得するためにも使用できます。

その際、lengthパラメーターを省略することで、開始位置から最後までを抽出できるのです。

SELECT SUBSTRING('Hello, World!' FROM 8);

このクエリは、’Hello, World!’という文字列から位置8から最後までを抽出します。

結果として’World!’が返されます。

文字列を後ろから取得する

PostgreSQLのsubstring関数は、直接的な逆順抽出をサポートしていません。

しかし、一時的に文字列を逆順にし、その上でsubstring関数を適用することで、文字列の後ろから取得することが可能です。

以下にその例を示します。

SELECT REVERSE(SUBSTRING(REVERSE('Hello, World!') FROM 1 FOR 5));

以下の順で処理されています。

REVERSE関数で、’Hello, World!’を逆順にする
SUBSTRING関数で、最初の5文字を取得する
REVERSE関数で、その結果を再度逆順にし、’Hello, World!’の後ろから5文字を取得する
結果として、’World’が返される

文字列を前から取得する

substring関数を用いて、文字列を前から取得する方法は既に説明しましたが、再度ここで紹介します。

以下のクエリでは、’Hello, World!’から前から6文字を取得可能です。

SELECT SUBSTRING('Hello, World!' FROM 1 FOR 6);

このクエリの結果は’Hello,’となります。

substring関数を活用した効率的なデータ管理

ここでは、「substring関数」を活用したデータ管理の効率化についてお伝えしていきます。

substring関数を活用することで、より柔軟で効果的なデータ操作が可能になるでしょう。

データクリーニングの実例
データ一貫性向上の実例
検索速度アップの実例

データクリーニングの実例

データベースには、さまざまな形式や規則でデータが保存されます。

一部のレコードに余分な空白が含まれている場合や、特定のパターンを含む文字列を検出したい場合など、substring関数はデータのクリーニングに非常に役立つでしょう。

SELECT SUBSTRING(products FROM '^\s*(.*?)\s*$') AS cleaned_product FROM your_table;

“products” カラムの値から余分な空白を取り除いたクリーニングされたデータを抽出。

正規表現パターン '^\s*(.*?)\s*$' を使用して、先頭と末尾の空白を取り除いています。

データ一貫性向上の実例

substring関数を用いると、一貫性のないデータを一貫性のある形式に変換することが可能です。

例えば、電話番号が一部のレコードで国番号が付与されていて、一部のレコードで国番号が付与されていない場合、substring関数と条件分岐を用いて一貫した形式に統一できます。

SELECT
    CASE
        WHEN phone_numbers ~ '^03\d{8}$' THEN '+81' || phone_numbers
        -- 国番号なしの場合、東京の地域コード '+81' を付与
        WHEN phone_numbers ~ '^(080|090|070)\d{8}$' THEN '+81' || phone_numbers
        -- 国番号なしの場合、携帯電話の地域コード '+81' を付与
        ELSE phone_numbers
    -- 既に国番号がある場合はそのまま使用
END AS formatted_phone_number
FROM
    your_table
;

上記のクエリでは、電話番号が以下の条件に一致する場合には国番号として ‘+81’ を付与します。

’03’ で始まり、その後に8桁の数字が続く場合（東京の地域コード）
‘080’、’090’、’070′ のいずれかで始まり、その後に8桁の数字が続く場合（携帯電話の地域コード）

検索速度アップの実例

特定のパターンを含むレコードを頻繁に検索する必要がある場合、substring関数を用いてそのパターンを抽出し、新たな列としてデータベースに保存することで、検索速度の向上が期待できます。

-- レコードから特定のパターンを抽出して新たな列として保存する
-- パターン：文字列の先頭から3文字を抽出する例
UPDATE
    your_table
SET
    extracted_pattern = SUBSTRING(
        column_name
        FROM
            1 FOR 3
    )
;

-- 新たな列に基づいて検索を行う
SELECT
    *
FROM
    your_table
WHERE
    extracted_pattern = 'ABC'
;