9.4. 文字列関数と演算子

本節では文字列の値の調査や操作のための関数と演算子について説明します。 ここでの文字列とはcharacterデータ型、character varyingデータ型、およびtextデータ型全ての値を含みます。補足説明のない限り、下記に挙げている全ての関数はこれら全てのデータ型に対して使用できますが、characterデータ型を使用した場合、自動的にパッドされるという副作用がありますので注意してください。一般的に、ここで説明する関数は文字列データ型でないデータであっても、最初に文字列表現に変換することで使用できます。ビット列データ型に特定した関数もいくつかあります。

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

表 9-5. SQL文字列関数と演算子

関数戻り値型説明結果
string || string text 文字列結合 'Post' || 'greSQL'PostgreSQL
bit_length(string)int文字列のビット数bit_length('jose')32
char_length(string) or character_length(string)int 文字列の中の文字数 char_length('jose')4
convert(string using conversion_name)text 指定した変換名でコード変換します。変換は、CREATE CONVERSIONを使用して定義できます。また、事前に定義されている変換名もいくつかあります。使用可能な変換名については、表9-7を参照してください。 convert('PostgreSQL' using iso_8859_1_to_utf8)UTF8(8ビット、Unicode)符号化方式での'PostgreSQL'
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)textPOSIX正規表現にマッチする副文字列を取り出します。パターンマッチに関してより詳細は、項9.7を参照してください。 substring('Thomas' from '...$')mas
substring(string from pattern for escape)textSQL正規表現にマッチする部分文字列を取り出します。パターンマッチに関してより詳細は、項9.7を参照してください。 substring('Thomas' from '%#"o_a#"_' for '#')oma
trim([leading | trailing | both] [characters] from string) textcharacters(デフォルトでは空白)で指定された文字のみを含む最も長い文字列を、stringの先頭、末尾、そしてその両方から削除します。 trim(both 'x' from 'xTomxx')Tom
upper(string)text文字列を大文字に変換upper('tom')TOM

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

表 9-6. その他の文字列関数

関数戻り値型説明結果
ascii(string)int引数の最初のバイトのASCIIコードascii('x')120
btrim(string text [, characters text])textcharacters(デフォルトは空白文字)で指定された文字のみを有する最長の文字列を、stringの先頭と末尾から削除します。 btrim('xyxtrimyyx', 'xy')trim
chr(int)text与えられたASCIIコードの文字chr(65)A
convert(string text, [src_encoding name,] dest_encoding name) text文字列をdest_encodingに変換します。元の符号化方式はsrc_encodingで指定されます。src_encodingが省略された場合はデータベースの符号化方式を採用します。 convert( 'text_in_utf8', 'UTF8', 'LATIN1')text_in_utf8 represented in ISO 8859-1 encoding
decode(string text, type text) bytea以前にencode()で符号化されたstringからバイナリデータを復号します。パラメータの型はencode()と同じです。 decode('MTIzAAE=', 'base64')123\000\001
encode(data bytea, type text) textバイナリデータを異なった表現方式に符号化します。サポートされている型は、base64hexescapeです。escapeは単にヌルバイトを\000として出力し、バックスラッシュを二重化します。 encode( E'123\\000\\001', 'base64')MTIzAAE=
initcap(string)textそれぞれの単語の第一文字を大文字にし、残りは小文字のまま残します。ここで単語とは、英数字以外の文字で区切られた、英数字からなる文字の並びのことです。 initcap('hi THOMAS')Hi Thomas
length(string)int string内の文字数 length('jose')4
lpad(string text, length int [, fill text]) text文字fill(デフォルトはスペース)を文字列の前に追加して、stringlengthの長さにします。stringが既にlengthの長さを超えている場合は(右側が)切り捨てられます。 lpad('hi', 5, 'xy')xyxhi
ltrim(string text [, characters text]) text stringの最初から、characters(デフォルトはsスペース)で指定された文字だけを有する最長の文字列を削除します。 ltrim('zzzytrim', 'xyz')trim
md5(string)textstringのMD5ハッシュを計算し、16進数で結果を返します。 md5('abc')900150983cd24fb0 d6963f7d28e17f72
pg_client_encoding()name 現在のクライアントの符号化方式の名前。 pg_client_encoding()SQL_ASCII
quote_ident(string)text与えられた文字列を、SQL問い合わせ文字列で識別子として使用できるように、適切な引用符を付けて返します。引用符は、必要な場合(例えば、文字列に識別子として使用できない文字が含まれる場合や、大文字変換される場合)にのみ追加されます。埋め込まれた引用符は、適切に二重になります。 quote_ident('Foo bar')"Foo bar"
quote_literal(string)text与えられた文字列を、SQL問い合わせ文字列で文字リテラルとして使用できるように、適切な引用符を付けて返します。 埋め込まれた単一引用符およびバックスラッシュは、適切に二重になります。 quote_literal( 'O\'Reilly')'O''Reilly'
regexp_replace(string text, pattern text, replacement text [,flags text])textPOSIX正規表現にマッチする副文字列を交換します。パターンマッチに関してより詳細は、項9.7を参照してください。 regexp_replace('Thomas', '.[mN]a.', 'M')ThM
repeat(string text, number int)textstringを指定number数分繰り返します。repeat('Pg', 4)PgPgPgPg
replace(string text, from text, to text)textstringに出現する全てのfrom部分文字列をto部分文字列に置換します。 replace( 'abcdefabcdef', 'cd', 'XX')abXXefabXXef
rpad(string text, length int [, fill text]) text文字fill(デフォルトはスペース)を文字列に追加して、stringlengthの長さにします。stringが既にlengthの長さを超えている場合は切り捨てられます。 rpad('hi', 5, 'xy')hixyx
rtrim(string text [, characters text]) textstringの末尾から、characters(デフォルトはスペース)で指定された文字のみを有する最長の文字列を削除します。 rtrim('trimxxxx', 'x')trim
split_part(string text, delimiter text, field int)textstringdelimiterで分割し、その結果から(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])textstringを他の符号化方式からASCIIに変換します。 (LATIN1LATIN2LATIN9WIN1250符号化方式からの変換のみをサポートします。) to_ascii('Karel')Karel
to_hex(number int or bigint)textnumberを、同等の16進数表現に変換します。 to_hex(2147483647)7fffffff
translate(string text, from text, to text) textfrom集合で指定された文字と一致するstringにある全ての文字は、それに対応するtoで指定された文字に置き換えられます。 translate('12345', '14', 'ax')a23x5

表 9-7. 組み込み済みの変換

変換名 [a] 変換前の符号化方式変換後の符号化方式
ascii_to_micSQL_ASCIIMULE_INTERNAL
ascii_to_utf8SQL_ASCIIUTF8
big5_to_euc_twBIG5EUC_TW
big5_to_micBIG5MULE_INTERNAL
big5_to_utf8BIG5UTF8
euc_cn_to_micEUC_CNMULE_INTERNAL
euc_cn_to_utf8EUC_CNUTF8
euc_jp_to_micEUC_JPMULE_INTERNAL
euc_jp_to_sjisEUC_JPSJIS
euc_jp_to_utf8EUC_JPUTF8
euc_kr_to_micEUC_KRMULE_INTERNAL
euc_kr_to_utf8EUC_KRUTF8
euc_tw_to_big5EUC_TWBIG5
euc_tw_to_micEUC_TWMULE_INTERNAL
euc_tw_to_utf8EUC_TWUTF8
gb18030_to_utf8GB18030UTF8
gbk_to_utf8GBKUTF8
iso_8859_10_to_utf8LATIN6UTF8
iso_8859_13_to_utf8LATIN7UTF8
iso_8859_14_to_utf8LATIN8UTF8
iso_8859_15_to_utf8LATIN9UTF8
iso_8859_16_to_utf8LATIN10UTF8
iso_8859_1_to_micLATIN1MULE_INTERNAL
iso_8859_1_to_utf8LATIN1UTF8
iso_8859_2_to_micLATIN2MULE_INTERNAL
iso_8859_2_to_utf8LATIN2UTF8
iso_8859_2_to_windows_1250LATIN2WIN1250
iso_8859_3_to_micLATIN3MULE_INTERNAL
iso_8859_3_to_utf8LATIN3UTF8
iso_8859_4_to_micLATIN4MULE_INTERNAL
iso_8859_4_to_utf8LATIN4UTF8
iso_8859_5_to_koi8_rISO_8859_5KOI8
iso_8859_5_to_micISO_8859_5MULE_INTERNAL
iso_8859_5_to_utf8ISO_8859_5UTF8
iso_8859_5_to_windows_1251ISO_8859_5WIN1251
iso_8859_5_to_windows_866ISO_8859_5WIN866
iso_8859_6_to_utf8ISO_8859_6UTF8
iso_8859_7_to_utf8ISO_8859_7UTF8
iso_8859_8_to_utf8ISO_8859_8UTF8
iso_8859_9_to_utf8LATIN5UTF8
johab_to_utf8JOHABUTF8
koi8_r_to_iso_8859_5KOI8ISO_8859_5
koi8_r_to_micKOI8MULE_INTERNAL
koi8_r_to_utf8KOI8UTF8
koi8_r_to_windows_1251KOI8WIN1251
koi8_r_to_windows_866KOI8WIN866
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_INTERNALWIN1251
mic_to_windows_866MULE_INTERNALWIN866
sjis_to_euc_jpSJISEUC_JP
sjis_to_micSJISMULE_INTERNAL
sjis_to_utf8SJISUTF8
tcvn_to_utf8WIN1258UTF8
uhc_to_utf8UHCUTF8
utf8_to_asciiUTF8SQL_ASCII
utf8_to_big5UTF8BIG5
utf8_to_euc_cnUTF8EUC_CN
utf8_to_euc_jpUTF8EUC_JP
utf8_to_euc_krUTF8EUC_KR
utf8_to_euc_twUTF8EUC_TW
utf8_to_gb18030UTF8GB18030
utf8_to_gbkUTF8GBK
utf8_to_iso_8859_1UTF8LATIN1
utf8_to_iso_8859_10UTF8LATIN6
utf8_to_iso_8859_13UTF8LATIN7
utf8_to_iso_8859_14UTF8LATIN8
utf8_to_iso_8859_15UTF8LATIN9
utf8_to_iso_8859_16UTF8LATIN10
utf8_to_iso_8859_2UTF8LATIN2
utf8_to_iso_8859_3UTF8LATIN3
utf8_to_iso_8859_4UTF8LATIN4
utf8_to_iso_8859_5UTF8ISO_8859_5
utf8_to_iso_8859_6UTF8ISO_8859_6
utf8_to_iso_8859_7UTF8ISO_8859_7
utf8_to_iso_8859_8UTF8ISO_8859_8
utf8_to_iso_8859_9UTF8LATIN5
utf8_to_johabUTF8JOHAB
utf8_to_koi8_rUTF8KOI8
utf8_to_sjisUTF8SJIS
utf8_to_tcvnUTF8WIN1258
utf8_to_uhcUTF8UHC
utf8_to_windows_1250UTF8WIN1250
utf8_to_windows_1251UTF8WIN1251
utf8_to_windows_1252UTF8WIN1252
utf8_to_windows_1253UTF8WIN1253
utf8_to_windows_1254UTF8WIN1254
utf8_to_windows_1255UTF8WIN1255
utf8_to_windows_1256UTF8WIN1256
utf8_to_windows_1257UTF8WIN1257
utf8_to_windows_866UTF8WIN866
utf8_to_windows_874UTF8WIN874
windows_1250_to_iso_8859_2WIN1250LATIN2
windows_1250_to_micWIN1250MULE_INTERNAL
windows_1250_to_utf8WIN1250UTF8
windows_1251_to_iso_8859_5WIN1251ISO_8859_5
windows_1251_to_koi8_rWIN1251KOI8
windows_1251_to_micWIN1251MULE_INTERNAL
windows_1251_to_utf8WIN1251UTF8
windows_1251_to_windows_866WIN1251WIN866
windows_1252_to_utf8WIN1252UTF8
windows_1256_to_utf8WIN1256UTF8
windows_866_to_iso_8859_5WIN866ISO_8859_5
windows_866_to_koi8_rWIN866KOI8
windows_866_to_micWIN866MULE_INTERNAL
windows_866_to_utf8WIN866UTF8
windows_866_to_windows_1251WIN866WIN1251
windows_874_to_utf8WIN874UTF8
注意:
a. 変換名は、標準命名体系に従っています。英数字以外の文字がアンダースコアで置換された変換前の符号化方式の公式名称の後ろに_to_が付けられ、その後に、同等に処理された変換後の符号化方式名が続きます。したがって、これらの名称は慣習的なコード名とは異なる可能性があります。