6.4. 文字列関数と演算子

この節では文字列の値の調査や操作のための関数と演算子について説明します。この文書の中で文字列とは CHARACTER データ型、CHARACTER VARYING データ型、および TEXT データ型すべてのデータ型の値を含みます。補足説明のない限り、下記に挙げてあるすべての関数はこれらすべてのデータ型に対して使用できますが、CHARACTER データ型を使用した場合自動的にパッドされる効果を生じますので注意してください。一般的に、ここで説明する関数は文字列データ型でないデータであっても、前もって文字列表現に変換しておけば使用できます。ビット列データ型に特定した関数も幾つかあります。

SQL では、引数の区切りにコンマではなく特定のキーワードを使う特殊な構文の文字列関数を、いくつか定義しています。詳細は Table 6-6 を参照してください。これら関数は通常の関数呼び出し構文によっても実装されています。Table 6-7 を参照。

Table 6-6. SQL 文字列関数と演算子

関数返り値型説明結果
string || string text 文字列結合 'Post' || 'greSQL'PostgreSQL
bit_length(string)integer文字列のビット数bit_length('jose')32
char_length(string) or character_length(string)integer 文字列の中の文字数 char_length('jose')4
convert(string using conversion_name)text (テキスト) 指定した変換名でコード変換します。 変換は、CREATE CONVERSION を使用して定義できます。 また、事前に定義されている変換名もいくつかあります。 使用可能な変換名については、Table 6-8 を参照してください。 convert('PostgreSQL' using iso_8859_1_to_utf_8)Unicode (UTF-8) でコード化された 'PostgreSQL'
lower(string)text (テキスト)文字列を小文字に変換lower('TOM')tom
octet_length(string)integer文字列のバイト数octet_length('jose')4
overlay(string placing string from integer [for integer])text (テキスト) 部分文字列の挿入 overlay('Txxxxas' placing 'hom' from 2 for 4)Thomas
position(substring in string)integer指定された部分文字列の場所position('om' in 'Thomas')3
substring(string [from integer] [for integer])text (テキスト) 部分文字列の取り出し substring('Thomas' from 2 for 3)hom
substring(string from pattern)text (テキスト) POSIX 正規表現にマッチする部分文字列の取り出し substring('Thomas' from '...$')mas
substring(string from pattern for escape)text (テキスト) SQL 正規表現にマッチする部分文字列の取り出し 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

このほか、Table 6-7 に列挙する文字列操作関数が使えます。 そのいくつかは、Table 6-6 で説明した SQL 標準文字列関数を実装するため、内部的に使用されます。

Table 6-7. その他の文字列関数

関数返り値型説明結果
ascii(text)integer引数の最初の文字の ASCII コードascii('x')120
btrim(string text, trim text)text (テキスト) trim で指定された文字のみを有する最長の文字列を、string の先頭と末尾から削除します。 btrim('xyxtrimyyx','xy')trim
chr(integer)text (テキスト)与えられた ASCII コードの文字chr(65)A
convert(string text, [src_encoding name,] dest_encoding name) text (テキスト) 文字列を dest_encoding に変換します。 元のコード化方式は src_encoding で指定されます。src_encoding が省略された場合はデータベースのコード化方式を採用します。 convert('text_in_unicode', 'UNICODE', 'LATIN1')ISO 8859-1 で表現される text_in_unicode
decode(string text, type text) bytea 以前に encode() で符号化された string からバイナリデータを復号します。 パラメータの型は encode() と同じです。 decode('MTIzAAE=', 'base64')123\000\001
encode(data bytea, type text) text (テキスト) バイナリデータを ASCII のみの表現に符号化します。 サポートされている型は、base64、hex、escape です。 encode('123\\000\\001', 'base64')MTIzAAE=
initcap(text)text (テキスト)(空白で区切られた) それぞれの単語の第一文字を大文字にします。initcap('hi thomas')Hi Thomas
length(string)integer 文字列の長さ length('jose')4
lpad(string text, length integer [, fill text]) text (テキスト) 文字 fill (デフォルトはスペース) を文字列の前に追加して、stringlength の長さにします。string が既に length の長さを越えている場合は(右側が) 切り捨てられます。 lpad('hi', 5, 'xy')xyxhi
ltrim(string text, text text)text (テキスト) 文字列の最初から、trim で指定された文字を有する最長の文字列を削除します。 ltrim('zzzytrim','xyz')trim
pg_client_encoding()name 現在のクライアントの符号化方式の名前。 pg_client_encoding()SQL_ASCII
quote_ident(string text)text (テキスト) 与えられた文字列を、SQL 問い合わせ文字列で識別子として使用できるように、適切な引用符を付けて返します。 引用符は、必要な場合 (たとえば、文字列に識別子のない文字が含まれる場合や、大文字変換される場合) にのみ追加されます。 埋め込まれた引用符は、適切に二重になります。 quote_ident('Foo')"Foo"
quote_literal(string text)text (テキスト) 与えられた文字列を、SQL 問い合わせ文字列でリテラルとして使用できるように、適切な引用符を付けて返します。 埋め込まれた引用符およびバックスラッシュは、適切に二重になります。 quote_literal('O\'Reilly')'O''Reilly'
repeat(text, integer)text (テキスト)テキストを指定回数分繰り返します。repeat('Pg', 4)PgPgPgPg
replace(string text, from text, to text)text (テキスト)string に出現するすべての from 部分文字列を to 部分文字列に置換します。replace('abcdefabcdef', 'cd', 'XX')abXXefabXXef
rpad(string text, length integer [, fill text]) text (テキスト) 文字 fill (デフォルトはスペース) を文字列に追加して、stringlength の長さにします。string が既に length の長さを越えている場合は切り捨てられます。 rpad('hi', 5, 'xy')hixyx
rtrim(string text, trim text)text (テキスト) 文字列の最後から、trim で指定された文字を有する最長の文字列を削除します。 rtrim('trimxxxx','x')trim
split_part(string text, delimiter text, column integer)text (テキスト)stringdelimiter で分割し、その結果から column 番目の文字列を返します。split_part('abc~@~def~@~ghi','~@~',2)def
strpos(string, substring)text (テキスト) 指定した部分文字列の位置を特定します (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(text [, encoding])text (テキスト) テキストを、他のコードから ASCII に変換します。 [a] to_ascii('Karel')Karel
to_hex(number integer or bigint)text (テキスト)number を、同等の 16 進数表現に変換します。 to_hex(9223372036854775807::bigint)7fffffffffffffff
translate(string text, from text, to text) text (テキスト)from で指定された文字と一致する string にあるすべての文字は、それに対応する to で指定された文字に置き換えられます。 translate('12345', '14', 'ax')a23x5
Notes:
a. to_ascii 関数は、LATIN1LATIN2、および WIN1250 からの変換のみをサポートしています。

Table 6-8. 埋め込み変換

変換名 [a] 変換前のコード変換後のコード
ascii_to_micSQL_ASCIIMULE_INTERNAL
ascii_to_utf_8SQL_ASCIIUNICODE
big5_to_euc_twBIG5EUC_TW
big5_to_micBIG5MULE_INTERNAL
big5_to_utf_8BIG5UNICODE
euc_cn_to_micEUC_CNMULE_INTERNAL
euc_cn_to_utf_8EUC_CNUNICODE
euc_jp_to_micEUC_JPMULE_INTERNAL
euc_jp_to_sjisEUC_JPSJIS
euc_jp_to_utf_8EUC_JPUNICODE
euc_kr_to_micEUC_KRMULE_INTERNAL
euc_kr_to_utf_8EUC_KRUNICODE
euc_tw_to_big5EUC_TWBIG5
euc_tw_to_micEUC_TWMULE_INTERNAL
euc_tw_to_utf_8EUC_TWUNICODE
gb18030_to_utf_8GB18030UNICODE
gbk_to_utf_8GBKUNICODE
iso_8859_10_to_utf_8LATIN6UNICODE
iso_8859_13_to_utf_8LATIN7UNICODE
iso_8859_14_to_utf_8LATIN8UNICODE
iso_8859_15_to_utf_8LATIN9UNICODE
iso_8859_16_to_utf_8LATIN10UNICODE
iso_8859_1_to_micLATIN1MULE_INTERNAL
iso_8859_1_to_utf_8LATIN1UNICODE
iso_8859_2_to_micLATIN2MULE_INTERNAL
iso_8859_2_to_utf_8LATIN2UNICODE
iso_8859_2_to_windows_1250LATIN2WIN1250
iso_8859_3_to_micLATIN3MULE_INTERNAL
iso_8859_3_to_utf_8LATIN3UNICODE
iso_8859_4_to_micLATIN4MULE_INTERNAL
iso_8859_4_to_utf_8LATIN4UNICODE
iso_8859_5_to_koi8_rISO_8859_5KOI8
iso_8859_5_to_micISO_8859_5MULE_INTERNAL
iso_8859_5_to_utf_8ISO_8859_5UNICODE
iso_8859_5_to_windows_1251ISO_8859_5WIN
iso_8859_5_to_windows_866ISO_8859_5ALT
iso_8859_6_to_utf_8ISO_8859_6UNICODE
iso_8859_7_to_utf_8ISO_8859_7UNICODE
iso_8859_8_to_utf_8ISO_8859_8UNICODE
iso_8859_9_to_utf_8LATIN5UNICODE
johab_to_utf_8JOHABUNICODE
koi8_r_to_iso_8859_5KOI8ISO_8859_5
koi8_r_to_micKOI8MULE_INTERNAL
koi8_r_to_utf_8KOI8UNICODE
koi8_r_to_windows_1251KOI8WIN
koi8_r_to_windows_866KOI8ALT
mic_to_asciiMULE_INTERNALSQL_ASCII
mic_to_big5MULE_INTERNALBIG5
mic_to_euc_cnMULE_INTERNALEUC_CN
mic_to_euc_jpMULE_INTERNALEUC_JP
mic_to_euc_krMULE_INTERNALEUC_KR
mic_to_euc_twMULE_INTERNALEUC_TW
mic_to_iso_8859_1MULE_INTERNALLATIN1
mic_to_iso_8859_2MULE_INTERNALLATIN2
mic_to_iso_8859_3MULE_INTERNALLATIN3
mic_to_iso_8859_4MULE_INTERNALLATIN4
mic_to_iso_8859_5MULE_INTERNALISO_8859_5
mic_to_koi8_rMULE_INTERNALKOI8
mic_to_sjisMULE_INTERNALSJIS
mic_to_windows_1250MULE_INTERNALWIN1250
mic_to_windows_1251MULE_INTERNALWIN
mic_to_windows_866MULE_INTERNALALT
sjis_to_euc_jpSJISEUC_JP
sjis_to_micSJISMULE_INTERNAL
sjis_to_utf_8SJISUNICODE
tcvn_to_utf_8TCVNUNICODE
uhc_to_utf_8UHCUNICODE
utf_8_to_asciiUNICODESQL_ASCII
utf_8_to_big5UNICODEBIG5
utf_8_to_euc_cnUNICODEEUC_CN
utf_8_to_euc_jpUNICODEEUC_JP
utf_8_to_euc_krUNICODEEUC_KR
utf_8_to_euc_twUNICODEEUC_TW
utf_8_to_gb18030UNICODEGB18030
utf_8_to_gbkUNICODEGBK
utf_8_to_iso_8859_1UNICODELATIN1
utf_8_to_iso_8859_10UNICODELATIN6
utf_8_to_iso_8859_13UNICODELATIN7
utf_8_to_iso_8859_14UNICODELATIN8
utf_8_to_iso_8859_15UNICODELATIN9
utf_8_to_iso_8859_16UNICODELATIN10
utf_8_to_iso_8859_2UNICODELATIN2
utf_8_to_iso_8859_3UNICODELATIN3
utf_8_to_iso_8859_4UNICODELATIN4
utf_8_to_iso_8859_5UNICODEISO_8859_5
utf_8_to_iso_8859_6UNICODEISO_8859_6
utf_8_to_iso_8859_7UNICODEISO_8859_7
utf_8_to_iso_8859_8UNICODEISO_8859_8
utf_8_to_iso_8859_9UNICODELATIN5
utf_8_to_johabUNICODEJOHAB
utf_8_to_koi8_rUNICODEKOI8
utf_8_to_sjisUNICODESJIS
utf_8_to_tcvnUNICODETCVN
utf_8_to_uhcUNICODEUHC
utf_8_to_windows_1250UNICODEWIN1250
utf_8_to_windows_1251UNICODEWIN
utf_8_to_windows_1256UNICODEWIN1256
utf_8_to_windows_866UNICODEALT
utf_8_to_windows_874UNICODEWIN874
windows_1250_to_iso_8859_2WIN1250LATIN2
windows_1250_to_micWIN1250MULE_INTERNAL
windows_1250_to_utf_8WIN1250UNICODE
windows_1251_to_iso_8859_5WINISO_8859_5
windows_1251_to_koi8_rWINKOI8
windows_1251_to_micWINMULE_INTERNAL
windows_1251_to_utf_8WINUNICODE
windows_1251_to_windows_866WINALT
windows_1256_to_utf_8WIN1256UNICODE
windows_866_to_iso_8859_5ALTISO_8859_5
windows_866_to_koi8_rALTKOI8
windows_866_to_micALTMULE_INTERNAL
windows_866_to_utf_8ALTUNICODE
windows_866_to_windows_1251ALTWIN
windows_874_to_utf_8WIN874UNICODE
Notes:
a. 変換名は、標準命名体系に従っています。 英数字以外の文字がアンダースコアで置換された変換前コードの公式名称の後ろに _to_ が付けられ、その後に、同等に処理された変換後のコード名が続きます。 したがって、これらの名称は、慣習的なコード名とは異なる可能性があります。