この節では文字列の値の調査や操作のための関数と演算子について説明します。この文書の中で文字列とは 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 (デフォルトはスペース) を文字列の前に追加して、string を length の長さにします。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 (デフォルトはスペース) を文字列に追加して、string を length の長さにします。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 (テキスト) | string を delimiter で分割し、その結果から 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 関数は、LATIN1、LATIN2、および WIN1250 からの変換のみをサポートしています。 | ||||
Table 6-8. 埋め込み変換
| 変換名 [a] | 変換前のコード | 変換後のコード |
|---|---|---|
| ascii_to_mic | SQL_ASCII | MULE_INTERNAL |
| ascii_to_utf_8 | SQL_ASCII | UNICODE |
| big5_to_euc_tw | BIG5 | EUC_TW |
| big5_to_mic | BIG5 | MULE_INTERNAL |
| big5_to_utf_8 | BIG5 | UNICODE |
| euc_cn_to_mic | EUC_CN | MULE_INTERNAL |
| euc_cn_to_utf_8 | EUC_CN | UNICODE |
| euc_jp_to_mic | EUC_JP | MULE_INTERNAL |
| euc_jp_to_sjis | EUC_JP | SJIS |
| euc_jp_to_utf_8 | EUC_JP | UNICODE |
| euc_kr_to_mic | EUC_KR | MULE_INTERNAL |
| euc_kr_to_utf_8 | EUC_KR | UNICODE |
| euc_tw_to_big5 | EUC_TW | BIG5 |
| euc_tw_to_mic | EUC_TW | MULE_INTERNAL |
| euc_tw_to_utf_8 | EUC_TW | UNICODE |
| gb18030_to_utf_8 | GB18030 | UNICODE |
| gbk_to_utf_8 | GBK | UNICODE |
| iso_8859_10_to_utf_8 | LATIN6 | UNICODE |
| iso_8859_13_to_utf_8 | LATIN7 | UNICODE |
| iso_8859_14_to_utf_8 | LATIN8 | UNICODE |
| iso_8859_15_to_utf_8 | LATIN9 | UNICODE |
| iso_8859_16_to_utf_8 | LATIN10 | UNICODE |
| iso_8859_1_to_mic | LATIN1 | MULE_INTERNAL |
| iso_8859_1_to_utf_8 | LATIN1 | UNICODE |
| iso_8859_2_to_mic | LATIN2 | MULE_INTERNAL |
| iso_8859_2_to_utf_8 | LATIN2 | UNICODE |
| iso_8859_2_to_windows_1250 | LATIN2 | WIN1250 |
| iso_8859_3_to_mic | LATIN3 | MULE_INTERNAL |
| iso_8859_3_to_utf_8 | LATIN3 | UNICODE |
| iso_8859_4_to_mic | LATIN4 | MULE_INTERNAL |
| iso_8859_4_to_utf_8 | LATIN4 | UNICODE |
| iso_8859_5_to_koi8_r | ISO_8859_5 | KOI8 |
| iso_8859_5_to_mic | ISO_8859_5 | MULE_INTERNAL |
| iso_8859_5_to_utf_8 | ISO_8859_5 | UNICODE |
| iso_8859_5_to_windows_1251 | ISO_8859_5 | WIN |
| iso_8859_5_to_windows_866 | ISO_8859_5 | ALT |
| iso_8859_6_to_utf_8 | ISO_8859_6 | UNICODE |
| iso_8859_7_to_utf_8 | ISO_8859_7 | UNICODE |
| iso_8859_8_to_utf_8 | ISO_8859_8 | UNICODE |
| iso_8859_9_to_utf_8 | LATIN5 | UNICODE |
| johab_to_utf_8 | JOHAB | UNICODE |
| koi8_r_to_iso_8859_5 | KOI8 | ISO_8859_5 |
| koi8_r_to_mic | KOI8 | MULE_INTERNAL |
| koi8_r_to_utf_8 | KOI8 | UNICODE |
| koi8_r_to_windows_1251 | KOI8 | WIN |
| koi8_r_to_windows_866 | KOI8 | ALT |
| 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 | KOI8 |
| mic_to_sjis | MULE_INTERNAL | SJIS |
| mic_to_windows_1250 | MULE_INTERNAL | WIN1250 |
| mic_to_windows_1251 | MULE_INTERNAL | WIN |
| mic_to_windows_866 | MULE_INTERNAL | ALT |
| sjis_to_euc_jp | SJIS | EUC_JP |
| sjis_to_mic | SJIS | MULE_INTERNAL |
| sjis_to_utf_8 | SJIS | UNICODE |
| tcvn_to_utf_8 | TCVN | UNICODE |
| uhc_to_utf_8 | UHC | UNICODE |
| utf_8_to_ascii | UNICODE | SQL_ASCII |
| utf_8_to_big5 | UNICODE | BIG5 |
| utf_8_to_euc_cn | UNICODE | EUC_CN |
| utf_8_to_euc_jp | UNICODE | EUC_JP |
| utf_8_to_euc_kr | UNICODE | EUC_KR |
| utf_8_to_euc_tw | UNICODE | EUC_TW |
| utf_8_to_gb18030 | UNICODE | GB18030 |
| utf_8_to_gbk | UNICODE | GBK |
| utf_8_to_iso_8859_1 | UNICODE | LATIN1 |
| utf_8_to_iso_8859_10 | UNICODE | LATIN6 |
| utf_8_to_iso_8859_13 | UNICODE | LATIN7 |
| utf_8_to_iso_8859_14 | UNICODE | LATIN8 |
| utf_8_to_iso_8859_15 | UNICODE | LATIN9 |
| utf_8_to_iso_8859_16 | UNICODE | LATIN10 |
| utf_8_to_iso_8859_2 | UNICODE | LATIN2 |
| utf_8_to_iso_8859_3 | UNICODE | LATIN3 |
| utf_8_to_iso_8859_4 | UNICODE | LATIN4 |
| utf_8_to_iso_8859_5 | UNICODE | ISO_8859_5 |
| utf_8_to_iso_8859_6 | UNICODE | ISO_8859_6 |
| utf_8_to_iso_8859_7 | UNICODE | ISO_8859_7 |
| utf_8_to_iso_8859_8 | UNICODE | ISO_8859_8 |
| utf_8_to_iso_8859_9 | UNICODE | LATIN5 |
| utf_8_to_johab | UNICODE | JOHAB |
| utf_8_to_koi8_r | UNICODE | KOI8 |
| utf_8_to_sjis | UNICODE | SJIS |
| utf_8_to_tcvn | UNICODE | TCVN |
| utf_8_to_uhc | UNICODE | UHC |
| utf_8_to_windows_1250 | UNICODE | WIN1250 |
| utf_8_to_windows_1251 | UNICODE | WIN |
| utf_8_to_windows_1256 | UNICODE | WIN1256 |
| utf_8_to_windows_866 | UNICODE | ALT |
| utf_8_to_windows_874 | UNICODE | WIN874 |
| windows_1250_to_iso_8859_2 | WIN1250 | LATIN2 |
| windows_1250_to_mic | WIN1250 | MULE_INTERNAL |
| windows_1250_to_utf_8 | WIN1250 | UNICODE |
| windows_1251_to_iso_8859_5 | WIN | ISO_8859_5 |
| windows_1251_to_koi8_r | WIN | KOI8 |
| windows_1251_to_mic | WIN | MULE_INTERNAL |
| windows_1251_to_utf_8 | WIN | UNICODE |
| windows_1251_to_windows_866 | WIN | ALT |
| windows_1256_to_utf_8 | WIN1256 | UNICODE |
| windows_866_to_iso_8859_5 | ALT | ISO_8859_5 |
| windows_866_to_koi8_r | ALT | KOI8 |
| windows_866_to_mic | ALT | MULE_INTERNAL |
| windows_866_to_utf_8 | ALT | UNICODE |
| windows_866_to_windows_1251 | ALT | WIN |
| windows_874_to_utf_8 | WIN874 | UNICODE |
| Notes: a. 変換名は、標準命名体系に従っています。 英数字以外の文字がアンダースコアで置換された変換前コードの公式名称の後ろに _to_ が付けられ、その後に、同等に処理された変換後のコード名が続きます。 したがって、これらの名称は、慣習的なコード名とは異なる可能性があります。 | ||