9.4. 字元串函數和操作符

本節描述了用於檢查和操作字元串數值的函數和操作符。 在這個環境中的字串包括所有類型 charactercharacter varying,和 text 的值。除非另外說明,所有下面列出的函數都可以處理這些類型, 不過要小心的是,在使用 character 類型的時候, 它的自動填充的潛在影響。通常這裡描述的函數也能用於非字串 類型,我們只要先把那些資料轉化為字串資料表現形式就可以了。 有些函數還可以處理位串類型。

SQL 定義了一些字串函數, 它們有指定的語法,它們裡面是用 某種特定的關鍵字,而不是逗號來分隔參數。 詳情請見Table 9-5, 這些函數也用正常的函數調用說法實現了。 (參閱 Table 9-6。)

Table 9-5. SQL 字串函數和操作符

函數返回類型描述例子結果
string || string text 字串連接 'Post' || 'greSQL'PostgreSQL
bit_length(string)integer字串裡二進制位的個數bit_length('jose')32
char_length(string)character_length(string)integer 字串中的字元個數 char_length('jose')4
convert(string using conversion_name)text 使用指定的轉換名字改變編碼。轉換可以透過 CREATE CONVERSION 定義。當然系統裡有一些預定義的轉換名字。參閱 Table 9-7 獲取可用的轉換名。 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
position(substring in string)integer聲明的子字串的位置position('om' in 'Thomas')3
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 從字串 string 的 開頭/結尾/兩邊/ 刪除只包含 characters (預設是一個空白)的最長的字串。 trim(both 'x' from 'xTomxx')Tom
upper(string)text把字串轉化為大寫。upper('tom')TOM

還有額外的字串操作函數可以用,它們在Table 9-6列出。 它們有些在內部用於實現Table 9-5列出的SQL標準字串函數。

Table 9-6. 其他字串函數

函數返回類型描述例子結果
ascii(text)integer參數第一個字元的 ASCIIascii('x')120
btrim(string text [, characters text])textstring 開頭和結尾刪除只包含在 characters 裡(預設是空白)的字元的最長字串。 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 形式的資料。 支援的類型有base64hexescapeencode('123\\000\\001', 'base64')MTIzAAE=
initcap(text)text 把每個單詞的第一個子母轉為大寫,其它的保留小寫。 單詞是一系列字母數字組成的字元,用非字母數字分隔。 initcap('hi thomas')Hi Thomas
length(string text)integer string 中字元的數目 length('jose')4
lpad(string text, length integer [, 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)text 計算給出 string 的 MD5 散列,以十六進制返回結果。 md5('abc')900150983cd24fb0d6963f7d28e17f72
pg_client_encoding()name 目前客戶端編碼名稱。 pg_client_encoding()SQL_ASCII
quote_ident(string text)text 返回給出字串的一個適用於在SQL語句字串裡當作標識符引起使用的形式。 只有在必要的時候才會添加引號(也就是說,如果字串包含非標識符字元或者會轉換大小寫的字元)。 嵌入的引號被恰當地寫了雙份。 quote_ident('Foo bar')"Foo bar"
quote_literal(string text)text 返回給出字串的一個適用於在SQL語句字串裡當作文本使用的形式。 嵌入的引號和反斜槓被恰當地寫了雙份。 quote_literal('O\'Reilly')'O''Reilly'
repeat(string text, number integer)text重複 string number 次。repeat('Pg', 4)PgPgPgPg
replace(string text, from text, to text)text把字串string裡出現地所有子字串 from 替換成子字串 toreplace('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 [, character text]) text 從字串 string 的 結尾刪除只包含 character (預設是個空白)的最長的字串。 rtrim('trimxxxx','x')trim
split_part(string text, delimiter text, field integer)text根據 delimiter 分隔 string 返回生成的第 field 個子字串(一為基)。 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])texttext 從其它編碼轉換為 ASCII[a] to_ascii('Karel')Karel
to_hex(number integer 或者 bigint)textnumber 轉換成其對應地十六進製資料表現形式。 to_hex(9223372036854775807)7fffffffffffffff
translate(string text, from text, to text) text 把在 string 中包含的任何匹配 from 中的字元的字元轉化為對應的 在 to 中的字元。 translate('12345', '14', 'ax')a23x5
Notes:
a. to_ascii 函數只支援從 LATIN1LATIN2LATIN9WIN1250 編碼進行轉換。

Table 9-7. 內置的轉換

轉換名 [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_,然後後面再跟著 經過同樣處理的目標編碼的名字。因此這些名字可能和客戶的編碼名字 不同。