★PostgreSQLカンファレンス2021 11月12日開催/チケット販売中★
他のバージョンの文書 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

9.4. 文字列関数と演算子

本節では文字列の値の調査や操作のための関数と演算子について説明します。 ここでの文字列とはcharacterデータ型、character varyingデータ型、およびtextデータ型の値を含みます。 補足説明のない限り、下記に挙げている全ての関数はtext型を受付、また戻り値型として返すように宣言されています。 それらはcharacter varyingデータ型も同じように受け付けます。 character型の値は関数あるいは演算子に適用される前にtextに変換され、character値の末尾の空白が削除されることになります。

SQLでは引数の区切りにカンマではなくキーワードを使用する文字列関数をいくつか定義しています。 詳細については表 9.9を参照してください。 またPostgreSQLは、これらの関数に対して通常の関数呼び出し構文を使用するバージョンを提供します(表 9.10を参照してください)。

注記

PostgreSQLの8.3より前において、これらの関数はいくつかの非文字列データ型の値を警告なしに受け付けたのは、それらデータ型を暗黙的にtext型に型変換していたことによります。 この強制的な変換は、頻繁に予期しない動作の原因となったので削除されました。 しかし、文字列連結演算子(||)は表 9.9で示されるように、少なくともひとつの入力が文字列型であれば、依然として非文字列入力を受け付けます。 その他の場合、以前と同じ動作が必要なら、textへの明示的な変換を行ってください。

表9.9 SQL文字列関数と演算子

関数/演算子

説明

text || texttext

2つの文字列を結合します。

'Post' || 'greSQL'PostgreSQL

text || anynonarraytext

anynonarray || texttext

非文字列の入力をテキストに変換したのちに2つの文字列を結合します。 (非文字列の入力は配列型であってはいけません。配列の||演算子との間で曖昧性が生じるからです。 配列のテキストあるいは類似のものを結合する場合は明示的にtextにキャストしてください。)

'Value: ' || 42Value: 42

text IS [NOT] [form] NORMALIZEDboolean

文字列が指定したUnicode正規形の範囲かどうかをチェックします。 オプションのformキーワードは正規形を指定します。NFC (デフォルトです)、NFDNFKCあるいはNFKDです。 この式はサーバエンコーディングがUTF8のときだけ使用できます。 この式を用いた正規形のチェックは、しばしばすでに正規化されている可能性のある文字列を正規化するよりも高速であることに注意してください。

U&'\0061\0308bc' IS NFD NORMALIZEDt

bit_length ( text ) → integer

文字列中のビット数を返します(octet_lengthの8倍です。)

bit_length('jose')32

char_length ( text ) → integer

character_length ( text ) → integer

文字列中の文字数を返します。

char_length('josé')4

lower ( text ) → text

データベースの照合順のルールに従い、文字列をすべて小文字に変換します。

lower('TOM')tom

normalize ( text [, form ] ) → text

文字列を指定したUnicode正規形に変換します。 オプションのformキーワードは正規形を指定します。NFC (デフォルトです)、NFDNFKCあるいはNFKDです。 この式はサーバエンコーディングがUTF8のときだけ使用できます。

normalize(U&'\0061\0308bc', NFC)U&'\00E4bc'

octet_length ( text ) → integer

文字列のバイト数を返します。

octet_length('josé') 5(サーバーエンコーディングがUTF8の場合)

octet_length ( character ) → integer

文字列のバイト数を返します。 このバージョンの関数は直接character型を受け付けるので、末尾の空白を削除しません。

octet_length('abc '::character(4))4

overlay ( string text PLACING newsubstring text FROM start integer [ FOR count integer ] ) → text

stringstart文字目からcount文字をnewsubstringで置き換えます。 countを省略するとnewsubstringの長さがデフォルトになります。

overlay('Txxxxas' placing 'hom' from 2 for 4)Thomas

position ( substring text IN string text ) → integer

string中のsubstringで指定する文字列開始位置を返します。0ならその文字列は存在しません。

position('om' in 'Thomas')3

substring ( string text [ FROM start integer ] [ FOR count integer ] ) → text

startが指定されていればstart番目の文字で始まるstringの部分文字列を返します。 countが指定されていればcount数の文字を取り出します。 少なくともstartcountのどちらかを指定してください。

substring('Thomas' from 2 for 3)hom

substring('Thomas' from 3)omas

substring('Thomas' for 2)Th

substring ( string text FROM pattern text ) → text

POSIX正規表現にマッチする部分文字列を返します。9.7.3を参照してください。

substring('Thomas' from '...$')mas

substring ( string text FROM pattern text FOR escape text ) → text

SQL正規表現にマッチする部分文字列を返します。9.7.2を参照してください。

substring('Thomas' from '%#"o_a#"_' for '#')oma

trim ( [ LEADING | TRAILING | BOTH ] [ characters text ] FROM string text ) → text

characters(空白一文字がデフォルトです)に現れる文字のみを含む最長の文字列をstringの最初、最後、あるいは両方(BOTHがデフォルトです)から取り除きます。

trim(both 'xyz' from 'yxTomxx')Tom

trim ( [ LEADING | TRAILING | BOTH ] [ FROM ] string text [, characters text ] ) → text

これはtrim()の非標準構文です。

trim(both from 'yxTomxx', 'xyz')Tom

upper ( text ) → text

データベースの照合順のルールに従い、文字列をすべて大文字に変換します。

upper('tom')TOM


この他、表 9.10に列挙する文字列操作関数が使えます。 そのいくつかは、表 9.9で列挙した標準SQLの文字列関数を実装するため、内部的に使用されます。

表9.10 その他の文字列関数

関数

説明

ascii ( text ) → integer

引数の最初の文字の数値コードを返します。 UTF8符号化方式ではその文字のUnicodeコードポイントを返します。 その他のマルチバイト符号化方式の場合、引数はASCII文字でなくてはなりません。

ascii('x')120

btrim ( string text [, characters text ] ) → text

characters(空白一文字がデフォルトです)に現れる文字のみを含む最長の文字列をstringの最初と最後から取り除きます。

btrim('xyxtrimyyx', 'xyz')trim

chr ( integer ) → text

与えられたコードの文字を返します。 UTF8符号化方式では、引数はUnicodeコードポイントと見なされます。 その他のマルチバイト符号化方式の場合、引数は指定のASCII文字でなくてはなりません。 chr(0)は禁止されています。テキストデータ型はその文字を格納できないからです。

chr(65)A

concat ( val1 "any" [, val2 "any" [, ...] ] ) → text

引数をテキスト形式にしたものを結合します。 NULL引数は無視されます。

concat('abcde', 2, NULL, 22)abcde222

concat_ws ( sep text, val1 "any" [, val2 "any" [, ...] ] ) → text

最初の引数以外をセパレータとともに結合します。 最初の引数はセパレータ文字列として使われ、NULLにすべきではありません。 それ以外のNULLの引数は無視されます。

concat_ws(',', 'abcde', 2, NULL, 22)abcde,2,22

format ( formatstr text [, formatarg "any" [, ...] ] ) → text

引数の書式をフォーマット文字列に従って整形します。 9.4.1を参照してください。 この関数はC言語関数のsprintfと似ています。

format('Hello %s, %1$s', 'World')Hello World, World

initcap ( text ) → text

それぞれの単語の第一文字を大文字に、残りは小文字に変換します。 ここで単語とは、英数字以外の文字で区切られた、英数字からなる文字の並びのことです。

initcap('hi THOMAS')Hi Thomas

left ( string text, n integer ) → text

文字列の先頭からn文字を返します。 nが負数の場合、文字列の末尾から|n|文字を切り取った文字列を返します。

left('abcde', 2)ab

length ( text ) → integer

文字列内の文字数を返します。

length('jose')4

lpad ( string text, length integer [, fill text ] ) → text

文字fill(デフォルトは空白文字)を文字列の前に追加して、stringlengthの長さにします。 stringが既にlengthの長さを超えている場合は(右側が)切り捨てられます。

lpad('hi', 5, 'xy')xyxhi

ltrim ( string text [, characters text ] ) → text

stringの最初から、characters(デフォルトは空白文字)で指定された文字だけを有する最長の文字列を削除します。

ltrim('zzzytest', 'xyz')test

md5 ( text ) → text

引数のMD5ハッシュ計算し、16進数で結果を返します。

md5('abc')900150983cd24fb0​d6963f7d28e17f72

parse_ident ( qualified_identifier text [, strict_mode boolean DEFAULT true ] ) → text[]

qualified_identifierを識別子の配列に分割し、個々の識別子に引用符があればそれを削除します。 デフォルトでは、最後の識別子の後に続く余分な文字はエラーとされますが、2番目のパラメータがfalseの場合は、そのような余分な文字は無視されます。 (この動作は、関数のようなオブジェクトに対して名前を解析するときに便利でしょう。) この関数は、長すぎる識別子を切り詰めないことに注意してください。 切り詰めが必要なときは、その結果をname[]にキャストすることができます。

parse_ident('"SomeSchema".someTable'){SomeSchema,sometable}

pg_client_encoding ( ) → name

現在のクライアントの符号化方式の名前を返します。

pg_client_encoding()UTF8

quote_ident ( text ) → text

与えられた文字列を、SQL問い合わせ文字列で識別子として使用できるように、適切な引用符を付けて返します。 引用符は、必要な場合(すなわち、文字列に識別子として使用できない文字が含まれる場合や、大文字変換される場合)にのみ追加されます。 埋め込まれた引用符は、適切に二重化されます。 例 42.1も参照してください。

quote_ident('Foo bar')"Foo bar"

quote_literal ( text ) → text

与えられた文字列を、SQL問い合わせ文字列で文字リテラルとして使用できるように、適切な引用符を付けて返します。 埋め込まれた単一引用符およびバックスラッシュは、適切に二重化されます。 quote_literalはNULL入力に対してNULLを返すことに注意してください。引数がNULLとなる可能性がある場合、よりquote_nullableの方がしばしば適しています。 例 42.1も参照してください。

quote_literal(E'O\'Reilly')'O''Reilly'

quote_literal ( anyelement ) → text

与えられた値をテキストに変換し、そしてリテラルとして引用符付けします。 埋め込まれた単一引用符とバックスラッシュは適切に二重化されます。

quote_literal(42.5)'42.5'

quote_nullable ( text ) → text

与えられた文字列を、SQL問い合わせ文字列で文字列リテラルとして使用できるように、適切な引用符を付けて返します。 また、引数がNULLの場合、NULLを返します。 埋め込まれた単一引用符およびバックスラッシュは適切に二重化されます。 例 42.1も参照してください。

quote_nullable(NULL)NULL

quote_nullable ( anyelement ) → text

与えられた値をテキストに変換し、そしてリテラルとして引用符付けします。引数がNULLの場合はNULLを返します。 埋め込まれた単一引用符とバックスラッシュは適切に二重化されます。

quote_nullable(42.5)'42.5'

regexp_match ( string text, pattern text [, flags text ] ) → text[]

stringに対してPOSIX正規表現でマッチし、捕捉された最初の部分文字列を返します。 より詳細は9.7.3を参照してください。

regexp_match('foobarbequebaz', '(bar)(beque)'){bar,beque}

regexp_matches ( string text, pattern text [, flags text ] ) → setof text[]

stringに対してPOSIX正規表現でマッチし、捕捉された部分文字列を返します。 より詳細は9.7.3を参照してください。

regexp_matches('foobarbequebaz', 'ba.', 'g')

 {bar}
 {baz}

regexp_replace ( string text, pattern text, replacement text [, flags text ] ) → text

POSIX正規表現に一致する部分文字列を置換します。 より詳細は9.7.3を参照してください。

regexp_replace('Thomas', '.[mN]a.', 'M')ThM

regexp_split_to_array ( string text, pattern text [, flags text ] ) → text[]

POSIX正規表現を区切り文字に使ってstringを分割します。 詳しくは9.7.3を参照ください。

regexp_split_to_array('hello world', '\s+'){hello,world}

regexp_split_to_table ( string text, pattern text [, flags text ] ) → setof text

POSIX正規表現を区切り文字に使ってstringを分割します。 詳しくは9.7.3を参照ください。

regexp_split_to_table('hello world', '\s+')

 hello
 world

repeat ( string text, number integer ) → text

指定されたnumberの数だけstringを繰り返します。

repeat('Pg', 4)PgPgPgPg

replace ( string text, from text, to text ) → text

stringに出現する全てのfrom部分文字列をto部分文字列に置換します。

replace('abcdefabcdef', 'cd', 'XX')abXXefabXXef

reverse ( text ) → text

文字列中の文字を逆順にします。

reverse('abcde')edcba

right ( string text, n integer ) ) → text

文字列の末尾からn文字を返します。 nが負数の場合は、文字列の先頭から|n|文字だけ切り取った文字列を返します。

right('abcde', 2)de

rpad ( string text, length integer [, fill text ] ) ) → text

文字fill(デフォルトは空白文字)を文字列に追加して、stringlengthの長さにします。 stringが既にlengthの長さを超えている場合は切り捨てられます。

rpad('hi', 5, 'xy')hixyx

rtrim ( string text [, characters text ] ) → text

stringの末尾から、characters(デフォルトは空白文字)で指定された文字のみを有する最長の文字列を削除します。

rtrim('testxxzx', 'xyz')test

split_part ( string text, delimiter text, n integer ) → text

stringdelimiterで分割し、その結果からn番目のフィールド(1から始まるように数える)を返します。

split_part('abc~@~def~@~ghi', '~@~', 2)def

strpos ( string text, substring text ) → integer

string中の指定したsubstringの開始位置を返します。substringが存在しなければゼロを返します。 (position(substring in string)と同じですが、引数の順序が逆であることに注意してください。)

strpos('high', 'ig')2

substr ( string text, start integer [, count integer ] ) → text

stringstart番目の文字から始まり、指定されている場合はcount文字だけ連続したが部分文字列を取り出します(substring(string from from for count)と同じです)。

substr('alphabet', 3)phabet

substr('alphabet', 3, 2)ph

starts_with ( string text, prefix text ) → boolean

stringprefixで始まっていれば真を返します。

starts_with('alphabet', 'alph')t

to_ascii ( string text ) → text

to_ascii ( string text, encoding name ) → text

to_ascii ( string text, encoding integer ) → text

stringを他の名前あるいは数で指定される符号化方式から、ASCIIに変換します。 encodingが省略されるとデータベースの符号化方式を指定したと見なします(これは実用的には唯一有用なケースです。) この変換は主にアクセントを削除するのが目的です。 LATIN1LATIN2LATIN9WIN1250符号化方式からの変換のみをサポートします。 (他のより柔軟な解決方法としては、unaccentモジュールを参照してください。)

to_ascii('Karél')Karel

to_hex ( integer ) → text

to_hex ( bigint ) → text

数を同等の16進数表現に変換します。

to_hex(2147483647)7fffffff

translate ( string text, from text, to text ) → text

from集合内の文字と一致するstringにある全ての文字は、to集合内のそれに対応する文字に置き換えられます。 もしfromtoより長い場合、fromで指定される余分な文字に一致するものは削除されます。

translate('12345', '143', 'ax')a2x5


concatconcat_wsおよびformat関数はVariadicです。従って、キーワードVARIADICで標しをつけられた配列のように、値を連結またはフォーマットした形で受け渡すことが可能です(37.5.5を参照してください)。 配列の要素は関数に対して分割された通常の引数のように扱われます。 もしvariadic配列引数がNULLであれば、concatおよびconcat_wsはNULLを返しますが、formatはNULLを要素を持たない配列と扱います。

9.21内のstring_agg集約関数と、文字列とbytea型を変換するための表 9.13内の関数も参照してください。

9.4.1. format

関数formatは、C関数のsprintf同様の形式で、フォーマット文字列に従ってフォーマットされた出力を生成します。

format(formatstr text [, formatarg "any" [, ...] ])

formatstrは結果がどのようにフォーマットされるかを指定するフォーマット文字列です。 フォーマット指示子が使用されている箇所を除き、フォーマット文字列のテキストは結果に直接コピーされます。 フォーマット指示子は文字列中のプレースホルダとして振舞い、その後に引き続く関数引数がどのようにフォーマットされ、どのように結果に挿入されるかを定義します。 それぞれのformatarg引数はそのデータ型に対する通常の出力規定に従ってテキストに変換され、その後フォーマット指示子に従って、結果文字列に挿入されます。

フォーマット指示子は%文字で始まり、以下の形式をとります。

%[position][flags][width]type

ここで要素フィールドとは以下になっています。

position (省略可能)

n$の形式の文字列で、nは出力する引数のインデックスです。 インデックス1はformatstrの後の最初の引数です。 positionが省略されると、一連の中の次の引数がデフォルトとして使用されます。

flags (省略可能)

フォーマット指示子の出力がどのようにフォーマットされるかを制御する追加の任意の要素です。 現在、サポートされているflagはマイナス記号(-)のみで、フォーマット指示子の出力が左詰めになるようにします。 これはwidthフィールドが同時に指定されていない場合は効果がありません。

width (省略可能)

フォーマット指示子の出力を表示する最小文字数を指定します。 出力は、幅を満たすのに必要な空白が左または右(flagの-による)に埋め込まれます。 幅が小さすぎても出力が切り詰められることはなく、単に無視されます。 幅は次のいずれかでも指定できます。それらは、正の整数、幅としての次の関数引数として使用する星印 (*)、またはn番目の関数引数を幅として使用する*n$という形式の文字列です。

幅を関数引数から取得する場合、その引数はフォーマット指示子の値に使用される引数より先に消費されます。 幅の引数が負の場合、フィールド長abs(width)の範囲内で結果は(あたかもflagで-が指定されたように)左詰めになります。

type (必須)

フォーマット指示子の出力を生成するのに使用されるフォーマット変換の型。 以下の型がサポートされています。

  • sは引数の値を単純文字列にフォーマットします。 NULL値は空文字列として扱われます。

  • Iは、引数をSQLの識別子として取り扱い、必要ならそれを二重引用符で括ります。 NULL値はエラーです(quote_identと同等です)。

  • Lは引数値をSQLリテラルとして引用符が付けられます。 NULL値は引用符無しの文字列NULLとなります(quote_nullableと同等です)。

上記で説明したフォーマット指示子に加え、特別の並びの%%がリテラル%文字を出力するために使用することもできます。

基本的なフォーマット変換の例を幾つか下記に紹介します。

SELECT format('Hello %s', 'World');
Result: Hello World

SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
Result: Testing one, two, three, %

SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
Result: INSERT INTO "Foo bar" VALUES('O''Reilly')

SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');
Result: INSERT INTO locations VALUES('C:\Program Files')

widthフィールドとflagの-を使用した例を以下に示します。

SELECT format('|%10s|', 'foo');
Result: |       foo|

SELECT format('|%-10s|', 'foo');
Result: |foo       |

SELECT format('|%*s|', 10, 'foo');
Result: |       foo|

SELECT format('|%*s|', -10, 'foo');
Result: |foo       |

SELECT format('|%-*s|', 10, 'foo');
Result: |foo       |

SELECT format('|%-*s|', -10, 'foo');
Result: |foo       |

以下の例はpositionフィールドの使い方を示しています。

SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
Result: Testing three, two, one

SELECT format('|%*2$s|', 'foo', 10, 'bar');
Result: |       bar|

SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
Result: |       foo|

標準C関数sprintfとは違って、PostgreSQLformat関数は、同一のフォーマット文字列の中でpositionフィールドがあるフォーマット指示子と、それがないフォーマット指示子の混在を許容します。 positionフィールドが無いフォーマット指示子は常に最終の引数が消費された後に次の引数を使用します。 さらに、format関数はフォーマット文字列で使用されるべき全ての関数引数を要求しません。 例を示します。

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Result: Testing three, two, three

%I および %Lのフォーマット指示子は特に動的SQL命令を安全に構築する場合に便利です。 例 42.1を参照してください。