本節では文字列の値の調査や操作のための関数と演算子について説明します。
ここでの文字列とはcharacterデータ型、character varyingデータ型、およびtextデータ型の値を含みます。
補足説明のない限り、下記に挙げている全ての関数はtext型を受付、また戻り値型として返すように宣言されています。
それらはcharacter varyingデータ型も同じように受け付けます。
character型の値は関数あるいは演算子に適用される前にtextに変換され、character値の末尾の空白が削除されることになります。
SQLでは引数の区切りにカンマではなくキーワードを使用する文字列関数をいくつか定義しています。 詳細については表 9.9を参照してください。 またPostgreSQLは、これらの関数に対して通常の関数呼び出し構文を使用するバージョンを提供します（表 9.10を参照してください）。
文字列連結演算子（||）は表 9.9で示されるように、少なくともひとつの入力が文字列型であれば、依然として非文字列入力を受け付けます。
その他の場合には非文字列入力を受け付けるために、textへの明示的な変換を行うことが可能です。
表9.9 SQL文字列関数と演算子
関数/演算子 説明 例 |
|---|
|
2つの文字列を結合します。
|
非文字列の入力をテキストに変換したのちに2つの文字列を結合します。
（非文字列の入力は配列型であってはいけません。配列の
|
|
文字列が指定したUnicode正規形の範囲かどうかをチェックします。
オプションの
|
|
文字列中のビット数を返します（
|
文字列中の文字数を返します。
|
|
データベースの照合順のルールに従い、文字列をすべて小文字に変換します。
|
文字
|
|
文字列を指定したUnicode正規形に変換します。
オプションの
|
文字列のバイト数を返します。
|
文字列のバイト数を返します。
このバージョンの関数は直接
|
|
|
文字
|
|
|
POSIX正規表現にマッチする最初の部分文字列を返します。9.7.3を参照してください。
|
SQL正規表現にマッチする最初の部分文字列を返します。9.7.2を参照してください。 最初の形式はSQL:2003以降で指定されています。2番目の形式はSQL:1999でのみ指定されており、廃れていると考えるべきでしょう。
|
|
これは
|
|
データベースの照合順のルールに従い、文字列をすべて大文字に変換します。
|
この他、表 9.10に列挙する文字列操作関数と演算子が使えます。 （そのいくつかは、表 9.9で列挙した標準SQLの文字列関数を実装するため、内部的に使用されます。） また、9.7で説明するしたパターンマッチ演算子と、第12章で説明する全文検索用の演算子もあります。
表9.10 その他の文字列操作関数と演算子
関数/演算子 説明 例 |
|---|
|
最初の文字列が2番目の文字列で始まる場合に真を返します（
|
|
引数の最初の文字の数値コードを返します。 UTF8符号化方式ではその文字のUnicodeコードポイントを返します。 その他のマルチバイト符号化方式の場合、引数はASCII文字でなくてはなりません。
|
|
与えられたコードの文字を返します。
UTF8符号化方式では、引数はUnicodeコードポイントと見なされます。
その他のマルチバイト符号化方式の場合、引数は指定のASCII文字でなくてはなりません。
|
引数をテキスト形式にしたものを結合します。 NULL引数は無視されます。
|
最初の引数以外をセパレータとともに結合します。 最初の引数はセパレータ文字列として使われ、NULLにすべきではありません。 それ以外のNULLの引数は無視されます。
|
引数の書式をフォーマット文字列に従って整形します。
9.4.1を参照してください。
この関数はC言語関数の
|
|
それぞれの単語の第一文字を大文字に、残りは小文字に変換します。 ここで単語とは、英数字以外の文字で区切られた、英数字からなる文字の並びのことです。
|
文字列の先頭から
|
|
文字列内の文字数を返します。
|
|
引数のMD5ハッシュ計算し、16進数で結果を返します。
|
|
|
現在のクライアントの符号化方式の名前を返します。
|
|
与えられた文字列を、SQL問い合わせ文字列で識別子として使用できるように、適切な引用符を付けて返します。 引用符は、必要な場合（すなわち、文字列に識別子として使用できない文字が含まれる場合や、大文字変換される場合）にのみ追加されます。 埋め込まれた引用符は、適切に二重化されます。 例 43.1も参照してください。
|
|
与えられた文字列を、SQL問い合わせ文字列で文字リテラルとして使用できるように、適切な引用符を付けて返します。
埋め込まれた単一引用符およびバックスラッシュは、適切に二重化されます。
|
与えられた値をテキストに変換し、そしてリテラルとして引用符付けします。 埋め込まれた単一引用符とバックスラッシュは適切に二重化されます。
|
与えられた文字列を、SQL問い合わせ文字列で文字列リテラルとして使用できるように、適切な引用符を付けて返します。
また、引数がNULLの場合、
|
与えられた値をテキストに変換し、そしてリテラルとして引用符付けします。引数がNULLの場合は
|
|
POSIX正規表現
|
POSIX正規表現
|
|
{bar}
{baz}
|
|
POSIX正規表現
|
POSIX正規表現を区切り文字に使って
|
POSIX正規表現を区切り文字に使って
hello world
|
POSIX正規表現
|
指定された
|
|
|
文字列中の文字を逆順にします。
|
文字列の末尾から
|
|
|
|
xx NULL zz
|
|
|
|
|
数を同等の16進数表現に変換します。
|
|
|
引数のエスケープされたUnicode文字を評価します。
Unicode文字は、 サーバのエンコーディングがUTF-8でなければ、これらのエスケープシーケンスで指定されるUnicodeコードポイントがサーバの実際のエンコーディングに変換されます。 変換不可能ならばエラーが報告されます。 この関数はUnicodeエスケープシーケンス（4.1.2.3参照）に対する（非標準の）代替を提供します。
|
concat、concat_wsおよびformat関数はVariadicです。従って、キーワードVARIADICで標しをつけられた配列のように、値を連結またはフォーマットした形で受け渡すことが可能です（38.5.6を参照してください）。
配列の要素は関数に対して分割された通常の引数のように扱われます。
もしvariadic配列引数がNULLであれば、concatおよびconcat_wsはNULLを返しますが、formatはNULLを要素を持たない配列と扱います。
9.21内のstring_agg集約関数と、文字列とbytea型を変換するための表 9.13内の関数も参照してください。
format #
関数formatは、C関数のsprintf同様の形式で、フォーマット文字列に従ってフォーマットされた出力を生成します。
format(formatstrtext[,formatarg"any"[, ...] ])
formatstrは結果がどのようにフォーマットされるかを指定するフォーマット文字列です。
フォーマット指示子が使用されている箇所を除き、フォーマット文字列のテキストは結果に直接コピーされます。
フォーマット指示子は文字列中のプレースホルダとして振舞い、その後に引き続く関数引数がどのようにフォーマットされ、どのように結果に挿入されるかを定義します。
それぞれのformatarg引数はそのデータ型に対する通常の出力規定に従ってテキストに変換され、その後フォーマット指示子に従って、結果文字列に挿入されます。
フォーマット指示子は%文字で始まり、以下の形式をとります。
%[position][flags][width]type
ここで要素フィールドとは以下になっています。
position (省略可能)
n$nは出力する引数のインデックスです。
インデックス１は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とは違って、PostgreSQLのformat関数は、同一のフォーマット文字列の中でpositionフィールドがあるフォーマット指示子と、それがないフォーマット指示子の混在を許容します。
positionフィールドが無いフォーマット指示子は常に最終の引数が消費された後に次の引数を使用します。
さらに、format関数はフォーマット文字列で使用されるべき全ての関数引数を要求しません。
例を示します。
SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Result: Testing three, two, three
%I および %Lのフォーマット指示子は特に動的SQL命令を安全に構築する場合に便利です。
例 43.1を参照してください。