9.4. 字串函式及運算子

本節介紹用於檢查和操作字串值的函數和運算子。此節中的字串包括 character，character varying 和 text 的內容。除非另有說明，否則下面列出的所有函數都適用於所有這些型別，但在使用字串型別時要小心自動填充字元的潛在影響。對於 bit-string 型別，一些函數也可以處理。

SQL 定義了一些字串函數，它們使用關鍵字而不是逗號來分隔參數。詳情見 Table 9.8。PostgreSQL 還提供了一般函數呼叫語法的這些函數的版本（參見 Table 9.9）。

注意 在 PostgreSQL 8.3 之前，由於存在從這些資料型別到文字的強制轉換，這些函數也會默默地接受幾個非字串資料型別的值。但這些強制措施已被刪除，因為它們經常引起令人驚訝的行為。不過，字串連接運算子（||）仍然接受非字串輸入，只要至少有一個輸入是字串型別，如 Table 9.8 所示。對於其他情況，如果需要複製先前的行為，請在語法中加入明確的轉換。

Table 9.8. SQL String Functions and Operators

Function	Return Type	Description	Example	Result
string || string	text	字串連接	'Post' || 'greSQL'	PostgreSQL
string || non-string or non-string ||string	text	字串與一個非字串輸入連接	'Value: ' || 42	Value: 42
bit_length(string)	int	字串中的位元數	bit_length('jose')	32
char_length(string) or 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 fromint [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] [forint])	text	提取子字串	substring('Thomas' from 2 for 3)	hom
substring(string from pattern)	text	提取符合 POSIX 正規表示式的子字串。有關特徵比對的更多訊息，請參見第 9.7 節。	substring('Thomas' from '...$')	mas
substring(string from pattern forescape)	text	提取符合 SQL 正規表示式的子字串。有關特徵比對的更多訊息，請參閱第 9.7 節。	substring('Thomas' from '%#"o_a#"_' for '#')	oma
trim([leading | trailing | both] [characters] from string)	text	從字串的開頭，結尾或兩端（兩者都是預設值）中刪除包含某些字元（預設為空格）的最長字串	trim(both 'xyz' from 'yxTomxx')	Tom
trim([leading | trailing | both] [from] string [, characters] )	text	trim() 的非標準語法	trim(both from 'yxTomxx', 'xyz')	Tom
upper(string)	text	將字串轉換為大寫	upper('tom')	TOM

還有其他字串操作函數可用，在 Table 9.9 中列出。 其中一些內部用於實作 SQL 標準的字串函數列在 Table 9.8。

Table 9.9. Other String Functions

Function	Return Type	Description	Example	Result
ascii(string)	int	參數的第一個字元的 ASCII 碼。對於 UTF8，回傳字元的 Unicode 代碼。對於其他多位元組編碼，參數必須是 ASCII 字元。	ascii('x')	120
btrim(string text [, characterstext])	text	從字串的開頭和結尾刪除特定字元的最長字串（預設為空格）	btrim('xyxtrimyyx', 'xyz')	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_encodingname)	bytea	將字串轉換為 dest_encoding。原始編碼由 src_encoding 指定。該字串必須在此編碼中有效。可以透過 CREATE CONVERSION 定義轉換。還有一些預定義的轉換。有關可用的轉換，請參閱 Table 9.10。	convert('text_in_utf8', 'UTF8', 'LATIN1')	text_in_utf8represented in Latin-1 encoding (ISO 8859-1)
convert_from(string bytea,src_encoding name)	text	將字串轉換為資料庫編碼。原始編碼由 src_encoding 指定。該字串必須在此編碼中有效。	convert_from('text_in_utf8', 'UTF8')	text_in_utf8represented in the current database encoding
convert_to(string text,dest_encoding name)	bytea	將字串轉換為 dest_encoding。	convert_to('some text', 'UTF8')	some text represented in the UTF8 encoding
decode(string text, format text)	bytea	從字串中的文字表示中解碼二進位資料。格式選項與編碼相同。	decode('MTIzAAE=', 'base64')	\x3132330001
encode(data bytea, format text)	text	將二進制資料編碼為文字表示。支援的格式為：base64，hex，escape。escape 將零位元組和 high-bit-set 位元組轉換為八進制序列（\nnn）並將倒斜線加倍。	encode(E'123\\000\\001', 'base64')	MTIzAAE=
format(formatstr text [,formatarg "any" [, ...] ])	text	根據格式字串格式化參數。此功能類似於 C 函數 sprintf。詳見 9.4.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	字串中的字元數	length('jose')	4
length(string bytea, encodingname )	int	給定編碼中字串中的字元數。該字串必須在此編碼中有效。	length('jose', 'UTF8')	4
lpad(string text, length int [,fill text])	text	透過在字元填充前加上字串填充（預設為空格）。 如果字串已經長於長度，那麼它將被截斷（在右側）。	lpad('hi', 5, 'xy')	xyxhi
ltrim(string text [, characterstext])	text	從字串的開頭刪除最長指定字元的字串（預設為空格）	ltrim('zzzytest', 'xyz')	test
md5(string)	text	計算字串的 MD5 雜湊值，以十六進位形式回傳結果	md5('abc')	900150983cd24fb0 d6963f7d28e17f72
parse_ident(qualified_identifiertext [, strictmode booleanDEFAULT true ] )	text[]	將 qualified_identifier 以標示字拆分為陣列，刪除任何單個標示字的引用。預設情況下，最後一個標示字後面的額外字元將被視為錯誤；但如果第二個參數為 false，則忽略這些額外的字元。（此行為對於解析函數等物件的名稱很有用。）請注意，此函數不會截斷超長標示字。如果要截斷，可以將結果轉換為 name[]。	parse_ident('"SomeSchema".someTable')	{SomeSchema,sometable}
pg_client_encoding()	name	目前用戶端的編碼名稱	pg_client_encoding()	SQL_ASCII
quote_ident(string text)	text	回傳適當引用的字串，以用作 SQL 語句字串中的標示字。僅在必要時加上引號（即如果字串包含非標示字或將被大小寫折疊）。 嵌入式引號會正確加倍。請參閱例 42.1。	quote_ident('Foo bar')	"Foo bar"
quote_literal(string text)	text	回傳適當引用的字串，以用作 SQL 語句字串中的字串文字。嵌入式單引號和倒斜線會適當加倍。請注意，quote_literal 在 null 輸入時回傳 null；如果參數可能為 null，則 quote_nullable 通常更合適。請參閱例 42.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。嵌入式單引號和倒斜線將適當加倍。請參閱例 42.1。	quote_nullable(NULL)	NULL
quote_nullable(value anyelement)	text	將給定的值強制轉換為文字型別，然後將其引用為文字；或者，如果參數為 null，則回傳 NULL。嵌入式單引號和倒斜線將適當加倍。	quote_nullable(42.5)	'42.5'
regexp_match(string text,pattern text [, flags text])	text[]	回傳由 POSIX 正規表示式與字串的第一個匹配產生的子字串。有關更多訊息，請參閱第 9.7.3 節。	regexp_match('foobarbequebaz', '(bar)(beque)')	{bar,beque}
regexp_matches(string text,pattern text [, flags text])	setof text[]	回傳透過將 POSIX 正規表示式與字串匹配而得到的子字串。有關更多訊息，請參閱第 9.7.3 節。	regexp_matches('foobarbequebaz', 'ba.', 'g')	{bar}{baz}(2 rows)
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(stringtext, pattern text [, flags text])	text[]	使用 POSIX 正規表示式作為分隔字拆分字串。有關更多訊息，請參閱第 9.7.3 節。	regexp_split_to_array('hello world', E'\\s+')	{hello,world}
regexp_split_to_table(stringtext, pattern text [, flagstext])	setof text	使用 POSIX 正規表示式作為分隔字拆分字串。有關更多訊息，請參閱第 9.7.3 節。	regexp_split_to_table('hello world', E'\\s+')	helloworld(2 rows)
repeat(string text, number int)	text	將字串重複的指定次數	repeat('Pg', 4)	PgPgPgPg
replace(string text, from text,to text)	text	以子字串 to 替換所有符合 from 的子字串	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（預設為空格）將字串填充至長度 length。如果字串已經長於 length，那麼它將被截斷。	rpad('hi', 5, 'xy')	hixyx
rtrim(string text [, characterstext])	text	從字串末尾刪除最長某包含 characters （預設為空格）的字串	rtrim('testxxzx', 'xyz')	test
split_part(string text,delimiter text, field int)	text	在分隔字上拆分字串並回傳給定段落（從一個字元開始）	split_part('abc~@~def~@~ghi', '~@~', 2)	def
strpos(string, substring)	int	回傳子字串的位置（與 position 相同，但請注意參數順序不同）	strpos('high', 'ig')	2
substr(string, from [, count])	text	提取子字串（與 substring 相同）	substr('alphabet', 3, 2)	ph
to_ascii(string text [, encodingtext])	text	從其他編碼將字串轉換為 ASCII（僅支援從 LATIN1，LATIN2，LATIN9 和 WIN1250 編碼轉換）	to_ascii('Karel')	Karel
to_hex(number int or bigint)	text	將數字轉換為其等效的十六進位表示	to_hex(2147483647)	7fffffff
translate(string text, fromtext, to text)	text	字串中與 from 集合中相符合的任何字元都將替換為 to 集合中的相對應字元。如果 from 長於 to，則會刪除 from 中出現的額外字元。	translate('12345', '143', 'ax')	a2x5

concat，concat_ws 和 format 函數是可變參數，因此可以將值連接或格式化成標記為 VARIADIC 關鍵字的陣列（請參閱第 37.4.5 節）。陣列的元素被視為它們是函數的單獨普通參數。如果 variadic 陣列參數為 NULL，則 concat 和 concat_ws 回傳 NULL，但 format 將 NULL 視為零元素陣列。

另請參閱第 9.20 節中的彙總函數 string_agg。

Table 9.10. Built-in Conversions

Conversion Name [a]	Source Encoding	Destination Encoding
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
utf8_to_euc_jis_2004	UTF8	EUC_JIS_2004
shift_jis_2004_to_utf8	SHIFT_JIS_2004	UTF8
utf8_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，後接類似處理的目標編碼名稱。因此，名稱可能會偏離慣用的編碼名稱。

9.4.1. format

函數格式化輸出根據格式字串的輸出，其格式類似於 C 函數 sprintf。

format(formatstr text [, formatarg "any" [, ...] ])

formatstr 是一個格式字串，指定如何格式化結果。格式字串中的文字將直接複製到結果中，除非使用格式標示符。格式標示符充當字串中的佔位符，定義後續函數參數應如何格式化並插入結果中。每個 formatarg 參數根據其資料型別的一般輸出規則轉換為文字，然後根據格式標示符進行格式化並插入到結果字串中。

格式標示符由 % 字元引入並具有其語法

%[position][flags][width]type

組件段落的位置：position（選擇性）

形式為 n$ 的字串，其中 n 是要輸入參數的索引。索引 1 表示 formatstr 之後的第一個參數。如果省略該位置，則預設使用 sequence.flags 中的下一個參數（選擇性）

控制格式標示符輸出格式的其他選項。目前唯一支援的標示是減號（ - ），這將使格式標示符的輸出向左對齊。除非還指定了 width，否則這沒有效果。（選擇性）

指定用於顯示格式標示符輸出的最小字元數。輸出在左側或右側（取決於 - 標示）填充，並根據需要填充空格以填充寬度。寬度太小不會導致截斷輸出，但會被忽略。可以使用以下任何一種來指定寬度：正整數；星號（）使用下一個函數參數作為寬度；或者 n$ 形式的字串，以使用第 n 個函數參數作為寬度。

如果寬度來自函數參數，則該參數在用於格式標示符值的參數之前使用。如果 width 參數為負，則結果在長度為 abs(width).type（必要）的段落內保持對齊（就像指定了 - 標誌一樣）。

用於産生格式標示符輸出的格式轉換型別。支援以下型別：

• s 將參數值格式化為簡單字串。空值被視為空字串。

• I 將參數值視為 SQL 標示符，必要時對其進行雙引號。值為 null（相當於 quote_ident）是一個錯誤。

• L 引用參數值作為 SQL 文字。空值顯示為字串 NULL，不帶引號（相當於 quote_nullable）。

除了上面描述的格式標示符之外，特殊序列 %% 可用於輸出文字 % 字元。

以下是基本格式轉換的一些範例：

SELECT format('Hello %s', 'World');
Result: Hello World

SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
Result: Testing one, two, three, %

SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
Result: INSERT INTO "Foo bar" VALUES('O''Reilly')

SELECT format('INSERT INTO %I VALUES(%L)', 'locations', E'C:\\Program Files');
Result: INSERT INTO locations VALUES(E'C:\\Program Files')

以下是使用寬度欄位和 - 標示的範例：

SELECT format('|%10s|', 'foo');
Result: |       foo|

SELECT format('|%-10s|', 'foo');
Result: |foo       |

SELECT format('|%*s|', 10, 'foo');
Result: |       foo|

SELECT format('|%*s|', -10, 'foo');
Result: |foo       |

SELECT format('|%-*s|', 10, 'foo');
Result: |foo       |

SELECT format('|%-*s|', -10, 'foo');
Result: |foo       |

這些範例顯示了 position 欄位的使用：

SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
Result: Testing three, two, one

SELECT format('|%*2$s|', 'foo', 10, 'bar');
Result: |       bar|

SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
Result: |       foo|

與標準 C 函數 sprintf 不同，PostgreSQL 的格式函數允許將具有和不具有位置欄位的格式標示符混合在相同的格式字串中。沒有位置欄位的格式標示符始終使用最後一個參數消耗後的下一個參數。此外，format 函數不要求在格式字串中使用所有函數參數。例如：

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Result: Testing three, two, three

%I 和 %L 格式標示符對於安全地建構動態 SQL 語句特別有用。詳見範例 42.1。