Menu

PostgreSQL 文字列関数:substring・concat・split_part

PostgreSQL で毎日使う文字列関数をまとめました。substring、left と right、|| と concat、split_part、position、replace、regexp_replace、trim、lpad、format を解説します。

このページのコードはエディタで実行できます。編集してすぐに結果を確認できます。

PostgreSQL でいちばんよく使う文字列関数です。

  • substring(text FROM start FOR length) は文字列の一部を切り出します。位置は 1 から始まります。
  • || または concat() は文字列を結合します。
  • split_part(text, delimiter, n) は区切られた文字列から1つのフィールドを取り出します。
  • replace(text, from, to) は部分文字列をすべて置き換えます。

Ada は Ada Lovelace とドメイン example.com になります。concat_ws は Lin の欠けている姓を飛ばすので、フルネームは末尾に空白のない Lin です。

文字列の一部を切り出す

結果は PostgreS、gre、Post、SQL、Postgre、0042 です。

  • substring(... FROM start FOR length) は標準 SQL の書き方で、substr(text, start, length) は同じことをする普通の関数です。長さを省くと残り全部を取り出します。
  • left や right に負の数を渡すと、最後 (または最初) の n 文字を除いたすべてになります。
  • FROM の後に正規表現を書くと、substring は最初に一致した部分を返します。パターンに括弧のグループがあれば、最初のグループを返します。

文字列を結合する: ||、concat、concat_ws

|| はどちらかが NULL だとすぐに NULL を返します。concat は NULL を空文字列として扱い ab を返します。concat_ws は NULL でない値の間に区切り文字を入れ、Ada, London を返します。|| の片方がテキストなら、もう片方の数値は変換されます (id-42 のように)。

複数の行の値を1つの文字列に結合するには string_agg を使います。

分割、検索、置換

結果は example.com、c、4、2026/11/17、a#b#c#、{red,green,blue} です。

  • split_part はフィールドが存在しないと空文字列を返します。負のフィールド番号は末尾から数えます (PostgreSQL 14 以降)。
  • position(sub IN text) と strpos(text, sub) は 1 から数えた位置を返し、見つからなければ 0 を返します。
  • replace はすべての出現を置き換え、大文字と小文字を区別します。regexp_replace は 'g' フラグを渡さない限り最初の一致だけを置き換えます。大文字と小文字を区別しないなら 'i' を加えます。
  • regexp_split_to_table(text, pattern) は配列ではなく行に分割します。

WHERE でのパターン判定 (LIKE、ILIKE、~) は LIKE、ILIKE と正規表現 を参照してください。

整形、埋め、書式指定

結果は hello、hi、00042、Hello World、5、Ada has 3 items です。ltrim と rtrim は片側だけを削ります。lpad と rpad は、目標の長さより長い文字列を切り詰めもします。

length は文字数を、octet_length はバイト数を数えます。é のような ASCII 以外の文字では両者が異なります。日本語の文字も同じです。format は値に %s、引用符付きの識別子に %I、引用符付きのリテラルに %L を使います。そのため関数の中で動的 SQL を組み立てるときの安全な方法になります。

早見表

やりたいこと関数結果の例
大文字化、小文字化upper(s), lower(s)ADA, ada
前方一致starts_with(s, 'pre') または s LIKE 'pre%'true
繰り返し、反転repeat('ab', 3), reverse('abc')ababab, cba
正規表現の最初の一致substring(s FROM 'pattern')一致したテキスト
正規表現のすべての一致regexp_matches(s, 'pattern', 'g')一致ごとに1行
数値を書式付きテキストにto_char(1234.5, 'FM9,999.00')1,234.50

残りの関数は 公式の文字列関数リファレンス に載っています。

よくある質問

PostgreSQL で文字列に部分文字列が含まれるか調べるには?

position('needle' IN haystack) > 0 か strpos(haystack, 'needle') > 0、WHERE 句なら haystack LIKE '%needle%' を使います。大文字と小文字を区別しないなら ILIKE、前方一致なら starts_with(haystack, 'prefix') です。

PostgreSQL で || が NULL を返すのはなぜですか?

|| のオペランドのどれかが NULL だと、結果は NULL になります。NULL の引数を飛ばす concat() や concat_ws() を使うか、NULL になりうる値を COALESCE(value, '') で包みます。

PostgreSQL の文字列の位置は 1 から始まりますか?

はい。位置は 1 から始まるので substring('abc' FROM 1 FOR 1) は a です。position は文字列が見つからないと 0 を返します。

PostgreSQL の text と varchar の違いは何ですか?

速度にも容量にも違いはありません。varchar(n) は長さのチェックを加えるだけで、text と長さ指定なしの varchar には、値1つあたり最大 1 GB という上限以外の制限はありません。どの文字列関数も両方を受け取ります。

Coddyのプログラミング言語のイラスト

Coddyでコードを学ぼう

始める