| PostgreSQL 9.1.5文書 | ||||
|---|---|---|---|---|
| 前のページ | 巻戻し | 第 9章関数と演算子 | 早送り | 次のページ |
本節では文字列の値の調査や操作のための関数と演算子について説明します。 ここでの文字列とはcharacterデータ型、character varyingデータ型、およびtextデータ型の値を含みます。補足説明のない限り、下記に挙げている全ての関数はこれら全てのデータ型に対して使用できますが、characterデータ型を使用した場合、自動的に空白文字がパッドされるという潜在的作用がありますので注意してください。バイナリ列データ型にたいしてもともとから存在するいくつかの関数もあります。
SQLでは引数の区切りにカンマではなくキーワードを使用する文字列関数をいくつか定義しています。 詳細については表9-5を参照してください。 またPostgreSQLは、これらの関数に対して通常の関数呼び出し構文を使用するバージョンを提供します（表9-6を参照してください）。
注意: PostgreSQL 8.3以前において、これらの関数はいくつかの非文字列データ型の値を警告なしに受け付けたのは、それらデータ型を暗黙的にtext型に型変換していたことによります。この強制的な変換は、頻繁に予期しない動作の原因となったので削除されました。しかし、文字列連結演算子（||）は表9-5で示されるように、少なくともひとつの入力が文字列型であれば、依然として非文字列入力を受け付けます。その他の場合、以前と同じ動作を期待するのなら、textへの明示的な変換を行ってください。
表 9-5. SQL文字列関数と演算子
| 関数 | 戻り値型 | 説明 | 例 | 結果 |
|---|---|---|---|---|
| string || string | text | 文字列結合 | 'Post' || 'greSQL' | PostgreSQL |
| 文字列 || 非文字列 または、 非文字列 || 文字列 | text | ひとつの非文字列のある入力の文字列結合 | 'Value: ' || 42 | Value: 42 |
bit_length(string)
| int | 文字列中のビット数 | bit_length('jose') | 32 |
char_length(string) または character_length(string)
| int | 文字列中の文字数 | char_length('jose') | 4 |
lower(string)
| text | 文字列を小文字に変換 | lower('TOM') | tom |
octet_length(string)
| int | 文字列中のバイト数 | octet_length('jose') | 4 |
overlay(string placing string from int [for int])
| text | 部分文字列の置換 | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas |
position(substring in string)
| int | 指定した部分文字列の場所 | position('om' in 'Thomas') | 3 |
substring(string [from int] [for int])
| text | 部分文字列の取り出し | substring('Thomas' from 2 for 3) | hom |
substring(string from pattern) | text | POSIX正規表現に一致する副文字列を取り出します。パターンマッチに関してより詳細は、項9.7を参照してください。 | substring('Thomas' from '...$') | mas |
substring(string from pattern for escape) | text | SQL正規表現に一致する部分文字列を取り出します。パターンマッチに関してより詳細は、項9.7を参照してください。 | substring('Thomas' from '%#"o_a#"_' for '#') | oma |
trim([leading | trailing | both]
[characters] from
string)
| text | characters（デフォルトでは空白）で指定された文字のみを含む最も長い文字列を、stringの先頭、末尾、そしてその両方から削除します。 | trim(both 'x' from 'xTomxx') | Tom |
upper(string)
| text | 文字列を大文字に変換 | upper('tom') | TOM |
この他、表9-6に列挙する文字列操作関数が使えます。そのいくつかは、表9-5で一覧した標準SQLの文字列関数を実装するため、内部的に使用されます。
表 9-6. その他の文字列関数
| 関数 | 戻り値型 | 説明 | 例 | 結果 |
|---|---|---|---|---|
ascii(string)
| int | 引数の最初の文字のASCIIコード。UTF8に対しては文字のUnicodeコードポイントを返します。その他のマルチバイト符号化方式の場合、引数はASCII文字でなくてはなりません。 | ascii('x') | 120 |
btrim(string text
[, characters text])
| text | characters（デフォルトは空白文字）で指定された文字のみを有する最長の文字列を、stringの先頭と末尾から削除します。 | btrim('xyxtrimyyx', 'xy') | trim |
chr(int)
| text | 与えられたコードの文字。UTF8では引数はUnicodeコードポイントとして扱われます。その他のマルチバイト符号化方式で引数はASCII文字である必要があります。 NULL (0)文字はテキストデータ型がそのようなバイトを格納することができないので許可されません。 | chr(65) | A |
concat(str "any"
[, str "any" [, ...] ])
| text | すべての引数の結合。ただしNULLは無視される。 | concat('abcde', 2, NULL, 22) | abcde222 |
concat_ws(sep text,
str "any"
[, str "any" [, ...] ])
| text | 第一引数を区切り文字とし、残りのすべての引数を結合する。最初の引数は区切り文字として使われる。NULLは無視される。 | concat_ws(',', 'abcde', 2, NULL, 22) | abcde,2,22 |
convert(string bytea,
src_encoding name,
dest_encoding name)
| bytea | 文字列をdest_encodingに変換します。元の符号化方式はsrc_encodingで指定されます。stringはこの符号化方式で有効でなければなりません。変換はCREATE CONVERSIONで定義されます。有効な変換については表9-7を参照してください。 | convert('text_in_utf8', 'UTF8', 'LATIN1') | Latin-1（ISO 8859-1）符号化方式のtext_in_utf8 |
convert_from(string bytea,
src_encoding name)
| text | データベースの符号化方式に文字列を変換します。元の符号化方式はsrc_encodingで指定されます。stringはこの符号化方式で有効なものでなければなりません。 | convert_from('text_in_utf8', 'UTF8') | 現在のデータベース符号化方式のtext_in_utf8 |
convert_to(string text,
dest_encoding name)
| bytea | 文字列をdest_encodingに変換します。 | convert_to('some text', 'UTF8') | UTF8符号化方式のsome text |
decode(string text,
format text)
| bytea |
stringからバイナリデータを復号します。オプションのformatはencode()と同じです。
| decode('MTIzAAE=', 'base64') | \x3132330001 |
encode(data bytea,
format text)
| text | バイナリデータをテキスト表現形式に符号化します。サポートされている形式は、base64、hex、escapeです。escapeは単にヌルバイトを\000のように出力し、バックスラッシュを二重化します。 | encode(E'123\\000\\001', 'base64') | MTIzAAE= |
format(formatstr text
[, str "any" [, ...] ])
| text | 文字列の書式を整えます。この関数はC言語関数のsprintfと似ています。ただし、次の変換指定子のみ受け付けます。
%sは該当する引数を文字列へ変換します。%Iは引数をSQL識別子へ変換します。%LはSQLリテラルへ変換します。
%%は%リテラルを出力します。変換では、変換指定子の前にn$を記述することで、明示的なパラメータの位置を参照することができます
nは引数の位置です。例39-1も参照してください。
| format('Hello %s, %1$s', 'World') | Hello World, World |
initcap(string)
| text | それぞれの単語の第一文字を大文字にし、残りは小文字のまま残します。ここで単語とは、英数字以外の文字で区切られた、英数字からなる文字の並びのことです。 | initcap('hi THOMAS') | Hi Thomas |
left(str text,
n int)
| text | 文字列の先頭からn文字を返します。nが負数の場合、|n|の数だけ文字列の末尾から切り取った文字列を返します。 | left('abcde', 2) | ab |
length(string)
| int | string内の文字数 | length('jose') | 4 |
length(stringbytea,
encoding name ) | int | 与えられたencodingでのstring内の文字数。stringはこの符号化方式で有効でなければなりません。 | length('jose', 'UTF8') | 4 |
lpad(string text,
length int
[, fill text])
| text | 文字fill（デフォルトは空白文字）を文字列の前に追加して、stringをlengthの長さにします。stringが既にlengthの長さを超えている場合は（右側が）切り捨てられます。 | lpad('hi', 5, 'xy') | xyxhi |
ltrim(string text
[, characters text])
| text | stringの最初から、characters（デフォルトはスペース）で指定された文字だけを有する最長の文字列を削除します。 | ltrim('zzzytrim', 'xyz') | trim |
md5(string)
| text | stringのMD5ハッシュを計算し、16進数で結果を返します。 | md5('abc') | 900150983cd24fb0 d6963f7d28e17f72 |
pg_client_encoding()
| name | 現在のクライアントの符号化方式の名前。 | pg_client_encoding() | SQL_ASCII |
quote_ident(string text)
| text | 与えられた文字列を、SQL問い合わせ文字列で識別子として使用できるように、適切な引用符を付けて返します。引用符は、必要な場合（例えば、文字列に識別子として使用できない文字が含まれる場合や、大文字変換される場合）にのみ追加されます。埋め込まれた引用符は、適切に二重化されます。 例39-1も参照してください。 | quote_ident('Foo bar') | "Foo bar" |
quote_literal(string text)
| text |
与えられた文字列を、SQL問い合わせ文字列で文字リテラルとして使用できるように、適切な引用符を付けて返します。埋め込まれた単一引用符およびバックスラッシュは、適切に二重化されます。
quote_literalはNULL入力に対してNULLを返すことに注意してください。引数がNULLとなる可能性がある場合、よりquote_nullableの方がしばしば適しています。
例39-1も参照してください。
| quote_literal(E'O\'Reilly') | 'O''Reilly' |
quote_literal(value anyelement) | text | 与えられた値をテキストに強制し、そしてリテラルとして引用符付けします。組み込まれた一重引用符と逆スラッシュは適切に二重化されます。 | quote_literal(42.5) | '42.5' |
quote_nullable(string text)
| text | 与えられた文字列を、SQL問い合わせ文字列で文字リテラルとして使用できるように、適切な引用符を付けて返します。 また、引数がNULLの場合、NULLを返します。 埋め込まれた単一引用符およびバックスラッシュは適切に二重化されます。 例39-1も参照してください。 | quote_nullable(NULL) | NULL |
quote_nullable(value anyelement) | text | 与えられた値をテキストに強制し、そしてリテラルとして引用符付けします。そうでないと、引数がNULLの場合はNULLを返します。埋め込まれた単一引用符とバックスラッシュは適切に二重化されます。 | quote_nullable(42.5) | '42.5' |
regexp_matches(string text, pattern text [, flags text])
| setof text[] | stringに対してPOSIX正規表現でマッチし、捕捉されたすべての部分文字列を返します。より詳細は項9.7.3を参照してください。 | regexp_matches('foobarbequebaz', '(bar)(beque)') | {bar,beque} |
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', E'\\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', E'\\s+') | hello world (2 rows) |
repeat(string text, number int)
| text | 指定されたnumberの数だけstringを繰り返します。 | repeat('Pg', 4) | PgPgPgPg |
replace(string text,
from text,
to text)
| text | stringに出現する全てのfrom部分文字列をto部分文字列に置換します。 | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef |
reverse(str)
| text | 逆順にした文字列を返します。 | reverse('abcde') | edcba |
right(str text,
n int)
| text | 文字列の末尾からn文字を返します。nが負数の場合は、|n|の数だけ文字列の末尾から切り取った文字列を返します。 | right('abcde', 2) | de |
rpad(string text,
length int
[, fill text])
| text | 文字fill（デフォルトはスペース）を文字列に追加して、stringをlengthの長さにします。stringが既にlengthの長さを超えている場合は切り捨てられます。 | rpad('hi', 5, 'xy') | hixyx |
rtrim(string text
[, characters text])
| text | stringの末尾から、characters（デフォルトはスペース）で指定された文字のみを有する最長の文字列を削除します。 | rtrim('trimxxxx', 'x') | trim |
split_part(string text,
delimiter text,
field int)
| text | stringをdelimiterで分割し、その結果から（1から始まるように数える）指定したフィールドを返します。 | split_part('abc~@~def~@~ghi', '~@~', 2) | def |
strpos(string, substring)
| int | 指定した部分文字列の位置を特定します（position(substring in string) と同じですが、引数の順序が逆になっていることに注意してください）。 | strpos('high', 'ig') | 2 |
substr(string, from [, count])
| text | 指定した部分文字列を取り出します（substring(string from from for count)と同じです）。 | substr('alphabet', 3, 2) | ph |
to_ascii(string text
[, encoding text])
| text | stringを他の符号化方式からASCIIに変換します。LATIN1、LATIN2、LATIN9、WIN1250符号化方式からの変換のみをサポートします。） | to_ascii('Karel') | Karel |
to_hex(number int
or bigint)
| text | numberを、同等の16進数表現に変換します。 | to_hex(2147483647) | 7fffffff |
translate(string text,
from text,
to text)
| text | from集合で指定された文字と一致するstringにある全ての文字は、それに対応するtoで指定された文字に置き換えられます。 もしfromがtoより長い場合、fromで指定される余分な文字に一致するものは削除されます。 | translate('12345', '143', 'ax') | a2x5 |
項9.18内のstring_agg集約関数も参照してください。
表 9-7. 組み込みの変換
| 変換名 [a] | 変換前の符号化方式 | 変換後の符号化方式 |
|---|---|---|
| ascii_to_mic | SQL_ASCII | MULE_INTERNAL |
| ascii_to_utf8 | SQL_ASCII | UTF8 |
| big5_to_euc_tw | BIG5 | EUC_TW |
| big5_to_mic | BIG5 | MULE_INTERNAL |
| big5_to_utf8 | BIG5 | UTF8 |
| euc_cn_to_mic | EUC_CN | MULE_INTERNAL |
| euc_cn_to_utf8 | EUC_CN | UTF8 |
| euc_jp_to_mic | EUC_JP | MULE_INTERNAL |
| euc_jp_to_sjis | EUC_JP | SJIS |
| euc_jp_to_utf8 | EUC_JP | UTF8 |
| euc_kr_to_mic | EUC_KR | MULE_INTERNAL |
| euc_kr_to_utf8 | EUC_KR | UTF8 |
| euc_tw_to_big5 | EUC_TW | BIG5 |
| euc_tw_to_mic | EUC_TW | MULE_INTERNAL |
| euc_tw_to_utf8 | EUC_TW | UTF8 |
| gb18030_to_utf8 | GB18030 | UTF8 |
| gbk_to_utf8 | GBK | UTF8 |
| iso_8859_10_to_utf8 | LATIN6 | UTF8 |
| iso_8859_13_to_utf8 | LATIN7 | UTF8 |
| iso_8859_14_to_utf8 | LATIN8 | UTF8 |
| iso_8859_15_to_utf8 | LATIN9 | UTF8 |
| iso_8859_16_to_utf8 | LATIN10 | UTF8 |
| iso_8859_1_to_mic | LATIN1 | MULE_INTERNAL |
| iso_8859_1_to_utf8 | LATIN1 | UTF8 |
| iso_8859_2_to_mic | LATIN2 | MULE_INTERNAL |
| iso_8859_2_to_utf8 | LATIN2 | UTF8 |
| iso_8859_2_to_windows_1250 | LATIN2 | WIN1250 |
| iso_8859_3_to_mic | LATIN3 | MULE_INTERNAL |
| iso_8859_3_to_utf8 | LATIN3 | UTF8 |
| iso_8859_4_to_mic | LATIN4 | MULE_INTERNAL |
| iso_8859_4_to_utf8 | LATIN4 | UTF8 |
| iso_8859_5_to_koi8_r | ISO_8859_5 | KOI8R |
| iso_8859_5_to_mic | ISO_8859_5 | MULE_INTERNAL |
| iso_8859_5_to_utf8 | ISO_8859_5 | UTF8 |
| iso_8859_5_to_windows_1251 | ISO_8859_5 | WIN1251 |
| iso_8859_5_to_windows_866 | ISO_8859_5 | WIN866 |
| iso_8859_6_to_utf8 | ISO_8859_6 | UTF8 |
| iso_8859_7_to_utf8 | ISO_8859_7 | UTF8 |
| iso_8859_8_to_utf8 | ISO_8859_8 | UTF8 |
| iso_8859_9_to_utf8 | LATIN5 | UTF8 |
| johab_to_utf8 | JOHAB | UTF8 |
| koi8_r_to_iso_8859_5 | KOI8R | ISO_8859_5 |
| koi8_r_to_mic | KOI8R | MULE_INTERNAL |
| koi8_r_to_utf8 | KOI8R | UTF8 |
| koi8_r_to_windows_1251 | KOI8R | WIN1251 |
| koi8_r_to_windows_866 | KOI8R | WIN866 |
| koi8_u_to_utf8 | KOI8U | UTF8 |
| mic_to_ascii | MULE_INTERNAL | SQL_ASCII |
| mic_to_big5 | MULE_INTERNAL | BIG5 |
| mic_to_euc_cn | MULE_INTERNAL | EUC_CN |
| mic_to_euc_jp | MULE_INTERNAL | EUC_JP |
| mic_to_euc_kr | MULE_INTERNAL | EUC_KR |
| mic_to_euc_tw | MULE_INTERNAL | EUC_TW |
| mic_to_iso_8859_1 | MULE_INTERNAL | LATIN1 |
| mic_to_iso_8859_2 | MULE_INTERNAL | LATIN2 |
| mic_to_iso_8859_3 | MULE_INTERNAL | LATIN3 |
| mic_to_iso_8859_4 | MULE_INTERNAL | LATIN4 |
| mic_to_iso_8859_5 | MULE_INTERNAL | ISO_8859_5 |
| mic_to_koi8_r | MULE_INTERNAL | KOI8R |
| mic_to_sjis | MULE_INTERNAL | SJIS |
| mic_to_windows_1250 | MULE_INTERNAL | WIN1250 |
| mic_to_windows_1251 | MULE_INTERNAL | WIN1251 |
| mic_to_windows_866 | MULE_INTERNAL | WIN866 |
| sjis_to_euc_jp | SJIS | EUC_JP |
| sjis_to_mic | SJIS | MULE_INTERNAL |
| sjis_to_utf8 | SJIS | UTF8 |
| tcvn_to_utf8 | WIN1258 | UTF8 |
| uhc_to_utf8 | UHC | UTF8 |
| utf8_to_ascii | UTF8 | SQL_ASCII |
| utf8_to_big5 | UTF8 | BIG5 |
| utf8_to_euc_cn | UTF8 | EUC_CN |
| utf8_to_euc_jp | UTF8 | EUC_JP |
| utf8_to_euc_kr | UTF8 | EUC_KR |
| utf8_to_euc_tw | UTF8 | EUC_TW |
| utf8_to_gb18030 | UTF8 | GB18030 |
| utf8_to_gbk | UTF8 | GBK |
| utf8_to_iso_8859_1 | UTF8 | LATIN1 |
| utf8_to_iso_8859_10 | UTF8 | LATIN6 |
| utf8_to_iso_8859_13 | UTF8 | LATIN7 |
| utf8_to_iso_8859_14 | UTF8 | LATIN8 |
| utf8_to_iso_8859_15 | UTF8 | LATIN9 |
| utf8_to_iso_8859_16 | UTF8 | LATIN10 |
| utf8_to_iso_8859_2 | UTF8 | LATIN2 |
| utf8_to_iso_8859_3 | UTF8 | LATIN3 |
| utf8_to_iso_8859_4 | UTF8 | LATIN4 |
| utf8_to_iso_8859_5 | UTF8 | ISO_8859_5 |
| utf8_to_iso_8859_6 | UTF8 | ISO_8859_6 |
| utf8_to_iso_8859_7 | UTF8 | ISO_8859_7 |
| utf8_to_iso_8859_8 | UTF8 | ISO_8859_8 |
| utf8_to_iso_8859_9 | UTF8 | LATIN5 |
| utf8_to_johab | UTF8 | JOHAB |
| utf8_to_koi8_r | UTF8 | KOI8R |
| utf8_to_koi8_u | UTF8 | KOI8U |
| utf8_to_sjis | UTF8 | SJIS |
| utf8_to_tcvn | UTF8 | WIN1258 |
| utf8_to_uhc | UTF8 | UHC |
| utf8_to_windows_1250 | UTF8 | WIN1250 |
| utf8_to_windows_1251 | UTF8 | WIN1251 |
| utf8_to_windows_1252 | UTF8 | WIN1252 |
| utf8_to_windows_1253 | UTF8 | WIN1253 |
| utf8_to_windows_1254 | UTF8 | WIN1254 |
| utf8_to_windows_1255 | UTF8 | WIN1255 |
| utf8_to_windows_1256 | UTF8 | WIN1256 |
| utf8_to_windows_1257 | UTF8 | WIN1257 |
| utf8_to_windows_866 | UTF8 | WIN866 |
| utf8_to_windows_874 | UTF8 | WIN874 |
| windows_1250_to_iso_8859_2 | WIN1250 | LATIN2 |
| windows_1250_to_mic | WIN1250 | MULE_INTERNAL |
| windows_1250_to_utf8 | WIN1250 | UTF8 |
| windows_1251_to_iso_8859_5 | WIN1251 | ISO_8859_5 |
| windows_1251_to_koi8_r | WIN1251 | KOI8R |
| windows_1251_to_mic | WIN1251 | MULE_INTERNAL |
| windows_1251_to_utf8 | WIN1251 | UTF8 |
| windows_1251_to_windows_866 | WIN1251 | WIN866 |
| windows_1252_to_utf8 | WIN1252 | UTF8 |
| windows_1256_to_utf8 | WIN1256 | UTF8 |
| windows_866_to_iso_8859_5 | WIN866 | ISO_8859_5 |
| windows_866_to_koi8_r | WIN866 | KOI8R |
| windows_866_to_mic | WIN866 | MULE_INTERNAL |
| windows_866_to_utf8 | WIN866 | UTF8 |
| windows_866_to_windows_1251 | WIN866 | WIN |
| windows_874_to_utf8 | WIN874 | UTF8 |
| euc_jis_2004_to_utf8 | EUC_JIS_2004 | UTF8 |
| ut8_to_euc_jis_2004 | UTF8 | EUC_JIS_2004 |
| shift_jis_2004_to_utf8 | SHIFT_JIS_2004 | UTF8 |
| ut8_to_shift_jis_2004 | UTF8 | SHIFT_JIS_2004 |
| euc_jis_2004_to_shift_jis_2004 | EUC_JIS_2004 | SHIFT_JIS_2004 |
| shift_jis_2004_to_euc_jis_2004 | SHIFT_JIS_2004 | EUC_JIS_2004 |
| 注意: a. 変換名は、標準命名体系に従っています。変換元符号化方式の公式名の内、英数字以外の文字をアンダースコアで置き換え、その後に_to_を付け、そして変換先符号化方式名を同様に処理したものを続けたものです。したがって、これらの名称は慣習的なコード名とは異なる可能性があります。 | ||