PostgreSQL 字符串函數(shù)和操作符

2021-08-26 17:59 更新
9.4.1. format

本節(jié)描述了用于檢查和操作字符串值的函數(shù)和操作符。 在這個(gè)環(huán)境中的串包括所有類型character、character varying和text的值。 除非特別說(shuō)明，這些函數(shù)和操作符聲明為接受并返回text類型。 他們將互換接受character varying參數(shù)。 在應(yīng)用函數(shù)或操作符之前character類型的值將被轉(zhuǎn)換為text，結(jié)果刪除character值中的任何末尾空格。

SQL定義了一些字符串函數(shù)，它們使用關(guān)鍵字，而不是逗號(hào)來(lái)分隔參數(shù)。詳情請(qǐng)見(jiàn)表 9.9，PostgreSQL也提供了這些函數(shù)使用正常函數(shù)調(diào)用語(yǔ)法的版本（見(jiàn)表 9.10）。

注意

由于存在從那些數(shù)據(jù)類型到text的隱式強(qiáng)制措施，在PostgreSQL 8.3之前，這些函數(shù)也可以接受多種非字符串?dāng)?shù)據(jù)類型。這些強(qiáng)制措施在目前的版本中已經(jīng)被刪除，因?yàn)樗鼈兂３?dǎo)致令人驚訝的行為。不過(guò)，字符串串接操作符（||）仍然接受非字符串輸入，只要至少一個(gè)輸入是一種字符串類型，如表 9.9所示。對(duì)于其他情況，如果你需要復(fù)制之前的行為，可以為text插入一個(gè)顯式強(qiáng)制措施。

表 9.9. SQL字符串函數(shù)和操作符

函數(shù)/操作符

描述

例子

text || text → text

連接兩個(gè)字符串。

'Post' || 'greSQL' → PostgreSQL

text || anynonarray → text

anynonarray || text → text

將非字符串輸入轉(zhuǎn)換為文本，然后將兩個(gè)字符串串聯(lián)在一起。 （非字符串輸入不能為數(shù)組類型，因?yàn)檫@將在||操作符的數(shù)組中造成歧義。如果你想連接一個(gè)數(shù)組的文本相等的，請(qǐng)顯式地將其轉(zhuǎn)換為text 。）

'Value: ' || 42 → Value: 42

text IS [NOT] [form] NORMALIZED → boolean

檢查字符串是否在指定的 Unicode 規(guī)范化表單中。 可選的form關(guān)鍵詞指定表單：NFC (默認(rèn)的), NFD, NFKC, 或 NFKD。 只有在服務(wù)器編碼為UTF8時(shí)，才能使用此表達(dá)式。 請(qǐng)注意，使用這個(gè)表達(dá)式檢查規(guī)范化通常比規(guī)范化可能已經(jīng)規(guī)范化的字符串要快。

U&'\0061\0308bc' IS NFD NORMALIZED → t

bit_length ( text ) → integer

返回字符串中的位數(shù)(8倍于octet_length)。

bit_length('jose') → 32

char_length ( text ) → integer

character_length ( text ) → integer

返回字符串中的字符數(shù)。

char_length('josé') → 4

lower ( text ) → text

根據(jù)數(shù)據(jù)庫(kù)的語(yǔ)言環(huán)境規(guī)則，將字符串轉(zhuǎn)換為全部小寫(xiě)。

lower('TOM') → tom

normalize ( text [, form ] ) → text

將字符串轉(zhuǎn)換為指定的Unicode規(guī)范化形式。 可選的form關(guān)鍵字指定了如下形式:NFC (the default)，NFD， NFKC，或NFKD。 該函數(shù)只能在服務(wù)器編碼為UTF8時(shí)使用。

normalize(U&'\0061\0308bc', NFC) → U&'\00E4bc'

octet_length ( text ) → integer

返回字符串的字節(jié)數(shù)。

octet_length('josé') → 5 (if server encoding is UTF8)

octet_length ( character ) → integer

返回字符串中的字節(jié)數(shù)。 由于此版本的函數(shù)直接接受character類型，它不會(huì)剝離尾隨空格。

octet_length('abc '::character(4)) → 4

overlay ( string text PLACING newsubstring text FROM start integer [ FOR count integer ] ) → text

替換string從start字符開(kāi)始的子串，并用newsubstring擴(kuò)展到count字符。 如果省略了count，則默認(rèn)為newsubstring的長(zhǎng)度。

overlay('Txxxxas' placing 'hom' from 2 for 4) → Thomas

position ( substring text IN string text ) → integer

返回指定的substring在string起始索引，如果不存在則返回零，。

position('om' in 'Thomas') → 3

substring ( string text [ FROM start integer ] [ FOR count integer ] ) → text

如果已指定，提取string從start字符開(kāi)始的子串， 并且在count字符后停止。如果已指定的話。 提供至少一個(gè)start和count中的至少一個(gè)。

substring('Thomas' from 2 for 3) → hom

substring('Thomas' from 3) → omas

substring('Thomas' for 2) → Th

substring ( string text FROM pattern text ) → text

提取匹配POSIX正則表達(dá)式的子字符串; 參見(jiàn) 第 9.7.3 節(jié)。

substring('Thomas' from '...$') → mas

substring ( string text FROM pattern text FOR escape text ) → text

提取匹配 SQL 正則表達(dá)式的字串；參見(jiàn) 第 9.7.2 節(jié)。

substring('Thomas' from '%#"o_a#"_' for '#') → oma

trim ( [ LEADING | TRAILING | BOTH ] [ characters text ] FROM string text ) → text

從string的開(kāi)始、末端或兩端(默認(rèn)為BOTH )移除僅包含characters(默認(rèn)為空格)字符的最長(zhǎng)字符串。

trim(both 'xyz' from 'yxTomxx') → Tom

trim ( [ LEADING | TRAILING | BOTH ] [ FROM ] string text [, characters text ] ) → text

這是一個(gè)非標(biāo)準(zhǔn)的trim()語(yǔ)法。

trim(both from 'yxTomxx', 'xyz') → Tom

upper ( text ) → text

根據(jù)數(shù)據(jù)庫(kù)的定位規(guī)則，將字符串轉(zhuǎn)換為所有大寫(xiě)。

upper('tom') → TOM


還有額外的串操作函數(shù)可以用，它們?cè)诒?nbsp;9.10中列出。它們有些在內(nèi)部用于實(shí)現(xiàn)表 9.9列出的SQL標(biāo)準(zhǔn)字符串函數(shù)。

表 9.10. 其他字符串函數(shù)

函數(shù)

描述

例子

ascii ( text ) → integer

返回參數(shù)的第一個(gè)字符的數(shù)字代碼。在UTF8編碼中，返回該字符的Unicode代碼點(diǎn)。 在其他多字節(jié)編碼中，該參數(shù)必須是一個(gè)ASCII字符。

ascii('x') → 120

btrim ( string text [, characters text ] ) → text

從string的開(kāi)頭或結(jié)尾刪除最長(zhǎng)的只包含characters（默認(rèn)是一個(gè)空格）的字符串

btrim('xyxtrimyyx', 'xyz') → trim

chr ( integer ) → text

返回給定代碼的字符。在UTF8編碼中該參數(shù)被視作一個(gè)Unicode代碼點(diǎn)。 在其他多字節(jié)編碼中該參數(shù)必須指定一個(gè)ASCII字符。 chr(0) 字符不被允許，因?yàn)槲谋緮?shù)據(jù)類型不能存儲(chǔ)這種字符。

chr(65) → A

concat ( val1 "any" [, val2 "any" [, ...] ] ) → text

連接所有參數(shù)的文本表示?？諈?shù)被忽略。

concat('abcde', 2, NULL, 22) → abcde222

concat_ws ( sep text, val1 "any" [, val2 "any" [, ...] ] ) → text

用分隔符連接除第一個(gè)參數(shù)外的所有參數(shù)。第一個(gè)參數(shù)用作分隔符字符串，不應(yīng)為NULL。其他NULL參數(shù)將被忽略。

concat_ws(',', 'abcde', 2, NULL, 22) → abcde,2,22

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

根據(jù)格式字符串對(duì)參數(shù)進(jìn)行格式化；參見(jiàn) 第 9.4.1 節(jié)。 這個(gè)函數(shù)類似于C函數(shù) sprintf。

format('Hello %s, %1$s', 'World') → Hello World, World

initcap ( text ) → text

將每個(gè)單詞的第一個(gè)字母轉(zhuǎn)換為大寫(xiě)，其余字母轉(zhuǎn)換為小寫(xiě)。單詞是由非字母數(shù)字字符分隔的字母數(shù)字字符序列。

initcap('hi THOMAS') → Hi Thomas

left ( string text, n integer ) → text

以字符串返回第一個(gè) n 字符，或在 n 為負(fù)時(shí), 返回最后 |n| 個(gè)字符之外的全部字符。

left('abcde', 2) → ab

length ( text ) → integer

返回字符串中的字符數(shù)。

length('jose') → 4

lpad ( string text, length integer [, fill text ] ) → text

將string擴(kuò)展為長(zhǎng)度length，通過(guò)前置字符fill（默認(rèn)空格）。 如果string已經(jīng)超過(guò)length那么它將被截?cái)啵ㄔ谟覀?cè)）。

lpad('hi', 5, 'xy') → xyxhi

ltrim ( string text [, characters text ] ) → text

從string開(kāi)始刪除包含characters（默認(rèn)空格）中僅包含字符的最長(zhǎng)字符串。

ltrim('zzzytest', 'xyz') → test

md5 ( text ) → text

計(jì)算參數(shù)的 MD5 hash ，結(jié)果以十六進(jìn)制形式寫(xiě)入。

md5('abc') → 900150983cd24fb0?d6963f7d28e17f72

parse_ident ( qualified_identifier text [, strict_mode boolean DEFAULT true ] ) → text[]

將qualified_identifier拆分為一個(gè)標(biāo)識(shí)符數(shù)組，刪除單個(gè)標(biāo)識(shí)符的任何引用。 默認(rèn)情況下，最后一個(gè)標(biāo)識(shí)符之后的額外字符被視為錯(cuò)誤；但是，如果第二個(gè)參數(shù)為false，則忽略這些額外的字符。 （這種行為對(duì)于解析類似函數(shù)的對(duì)象的名稱有作用。） 請(qǐng)注意，此函數(shù)不會(huì)截?cái)喑L(zhǎng)標(biāo)識(shí)符。如果你想截?cái)?，你可以把結(jié)果給到name[]。

parse_ident('"SomeSchema".someTable') → {SomeSchema,sometable}

pg_client_encoding ( ) → name

返回當(dāng)前客戶端編碼名稱。

pg_client_encoding() → UTF8

quote_ident ( text ) → text

返回適合引用的給定字符串，作為SQL語(yǔ)句字符串中的標(biāo)識(shí)符。 只有在必要的情況下才添加引號(hào)(例如，如果字符串包含非標(biāo)識(shí)符字符或?qū)⒈淮笮?xiě)折疊)。 嵌入的引號(hào)被適當(dāng)?shù)丶与p引號(hào)。參見(jiàn)例 42.1。

quote_ident('Foo bar') → "Foo bar"

quote_literal ( text ) → text

返回在SQL語(yǔ)句字符串中適當(dāng)引用的給定字符串，用作字符串文字使用。 嵌入式單引號(hào)和反斜線適當(dāng)?shù)姆?轉(zhuǎn)雙引號(hào)或雙斜線)。 請(qǐng)注意，quote_literal返回?zé)o效輸入；如果這個(gè)參數(shù)可能為空，quote_nullable通常更合適。另請(qǐng)參閱例 42.1。

quote_literal(E'O\'Reilly') → 'O''Reilly'

quote_literal ( anyelement ) → text

將給定的值轉(zhuǎn)換為文本，然后將其作為字面量引用。 內(nèi)嵌的單引號(hào)和反斜杠被適當(dāng)?shù)胤丁?

quote_literal(42.5) → '42.5'

quote_nullable ( text ) → text

返回在SQL語(yǔ)句字符串中適當(dāng)引用的給定字符串文字;或者，如果參數(shù)為null，則返回NULL。 內(nèi)嵌的單引號(hào)和反斜杠被適當(dāng)?shù)胤丁⒁?jiàn)例 42.1。

quote_nullable(NULL) → NULL

quote_nullable ( anyelement ) → text

將給定值轉(zhuǎn)換為文本，然后將其作為字面量引用；或者，如果參數(shù)為null，則返回NULL。 內(nèi)嵌的單引號(hào)和反斜杠被適當(dāng)?shù)胤丁?

quote_nullable(42.5) → '42.5'

regexp_match ( string text, pattern text [, flags text ] ) → text[]

返回從POSIX正則表達(dá)式到string的第一個(gè)匹配中捕獲的子字符串;參見(jiàn)第 9.7.3 節(jié)。

regexp_match('foobarbequebaz', '(bar)(beque)') → {bar,beque}

regexp_matches ( string text, pattern text [, flags text ] ) → setof text[]

返回通過(guò)將POSIX正則表達(dá)式與string匹配而捕獲的子字符串；參見(jiàn)第 9.7.3 節(jié)。

regexp_matches('foobarbequebaz', 'ba.', 'g') → 

 {bar}
 {baz}

regexp_replace ( string text, pattern text, replacement text [, flags text ] ) → text

替換匹配POSIX正則表達(dá)式的子字符串；參見(jiàn) 第 9.7.3 節(jié)。

regexp_replace('Thomas', '.[mN]a.', 'M') → ThM

regexp_split_to_array ( string text, pattern text [, flags text ] ) → text[]

使用POSIX正則表達(dá)式作為分隔符拆分string； 參見(jiàn) 第 9.7.3 節(jié)。

regexp_split_to_array('hello world', '\s+') → {hello,world}

regexp_split_to_table ( string text, pattern text [, flags text ] ) → setof text

使用POSIX正則表達(dá)式作為分隔符拆分string；參見(jiàn) 第 9.7.3 節(jié)。

regexp_split_to_table('hello world', '\s+') → 

 hello
 world

repeat ( string text, number integer ) → text

重復(fù)string指定number的次數(shù)。

repeat('Pg', 4) → PgPgPgPg

replace ( string text, from text, to text ) → text

將string 中當(dāng)前的子串from替換為子串to。

replace('abcdefabcdef', 'cd', 'XX') → abXXefabXXef

reverse ( text ) → text

顛倒字符串中字符的順序。

reverse('abcde') → edcba

right ( string text, n integer ) ) → text

返回字符串中的最后n個(gè)字符，或者在n>為負(fù)時(shí)，返回除了前面的|n|字符之外的所有字符。

right('abcde', 2) → de

rpad ( string text, length integer [, fill text ] ) ) → text

擴(kuò)展 string 到長(zhǎng)度 length，通過(guò)追加fill 字符(默認(rèn)為空格). 如果string 已經(jīng)比 length 長(zhǎng)，則截?cái)嗨?

rpad('hi', 5, 'xy') → hixyx

rtrim ( string text [, characters text ] ) → text

從string末尾刪除包含characters（默認(rèn)為空格）中僅包含字符的最長(zhǎng)字符串。

rtrim('testxxzx', 'xyz') → test

split_part ( string text, delimiter text, n integer ) → text

在delimiter出現(xiàn)時(shí)拆分string，并且返回第n個(gè)字段(從一計(jì)數(shù))。

split_part('abc~@~def~@~ghi', '~@~', 2) → def

strpos ( string text, substring text ) → integer

返回在string中指定的substring的起始索引,如果不存在則為零。 （與(substring 在 string中的)位置相同，但是請(qǐng)注意反轉(zhuǎn)的參數(shù)順序）

strpos('high', 'ig') → 2

substr ( string text, start integer [, count integer ] ) → text

提取string從start字符開(kāi)始的子字符串，并擴(kuò)展count字符，如果指定了的話。 (與 子字符串(string 從 start 開(kāi)始計(jì)數(shù) count)相同。)

substr('alphabet', 3) → phabet

substr('alphabet', 3, 2) → ph

starts_with ( string text, prefix text ) → boolean

如果 string 以 prefix開(kāi)始就返回真。

starts_with('alphabet', 'alph') → t

to_ascii ( string text ) → text

to_ascii ( string text, encoding name ) → text

to_ascii ( string text, encoding integer ) → text

將string從另一個(gè)編碼中轉(zhuǎn)換為ASCII，該編碼可按名稱或編號(hào)標(biāo)識(shí)。 如果encoding被省略，則假定數(shù)據(jù)庫(kù)編碼（這在實(shí)踐中是唯一有用的案例）。轉(zhuǎn)換主要包括降音。 轉(zhuǎn)換僅支持來(lái)自 LATIN1、LATIN2、LATIN9、 和 WIN1250 的編碼. (其他請(qǐng)參見(jiàn) unaccent 模塊, 更靈活的解決方案。)

to_ascii('Karél') → Karel

to_hex ( integer ) → text

to_hex ( bigint ) → text

將數(shù)字轉(zhuǎn)換為其相應(yīng)的十六進(jìn)制表示形式。

to_hex(2147483647) → 7fffffff

translate ( string text, from text, to text ) → text

將string中與from集合中匹配的每個(gè)字符替換為to集合中相應(yīng)的字符。 如果from長(zhǎng)于to，from中出現(xiàn)的額外字符被刪除。

translate('12345', '143', 'ax') → a2x5


concat、concat_ws和format函數(shù)是可變的，因此可以把要串接或格式化的值作為一個(gè)標(biāo)記了VARIADIC關(guān)鍵字的數(shù)組進(jìn)行傳遞（見(jiàn)第 37.5.5 節(jié)）。 數(shù)組的元素被當(dāng)作函數(shù)的獨(dú)立普通參數(shù)一樣處理。如果可變數(shù)組參數(shù)為 NULL，concat和concat_ws返回 NULL，但format把 NULL 當(dāng)作一個(gè)零元素?cái)?shù)組。

還可以參閱第 9.21 節(jié)中的string_agg，以及表 9.13中的字符串和bytea類型之間轉(zhuǎn)換的功能。

9.4.1. format

函數(shù)format根據(jù)一個(gè)格式字符串產(chǎn)生格式化的輸出，其形式類似于 C 函數(shù)sprintf。 

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

formatstr是一個(gè)格式字符串，它指定了結(jié)果應(yīng)該如何被格式化。格式字符串中的文本被直接復(fù)制到結(jié)果中，除了使用格式說(shuō)明符的地方。格式說(shuō)明符在字符串中扮演著占位符的角色，它定義后續(xù)的函數(shù)參數(shù)如何被格式化及插入到結(jié)果中。每一個(gè)formatarg參數(shù)會(huì)被根據(jù)其數(shù)據(jù)類型的常規(guī)輸出規(guī)則轉(zhuǎn)換為文本，并接著根據(jù)格式說(shuō)明符被格式化和插入到結(jié)果字符串中。

格式說(shuō)明符由一個(gè)%字符開(kāi)始并且有這樣的形式 

%[position][flags][width]type

其中的各組件域是：

position（可選）

一個(gè)形式為n$的字符串，其中n是要打印的參數(shù)的索引。索引 1 表示formatstr之后的第一個(gè)參數(shù)。如果position被忽略，默認(rèn)會(huì)使用序列中的下一個(gè)參數(shù)。

flags（可選）

控制格式說(shuō)明符的輸出如何被格式化的附加選項(xiàng)。當(dāng)前唯一支持的標(biāo)志是一個(gè)負(fù)號(hào)（-），它將導(dǎo)致格式說(shuō)明符的輸出會(huì)被左對(duì)齊（left-justified）。除非width域也被指定，否者這個(gè)域不會(huì)產(chǎn)生任何效果。

width（可選）

指定用于顯示格式說(shuō)明符輸出的最小字符數(shù)。輸出將被在左部或右部（取決于-標(biāo)志）用空格填充以保證充滿該寬度。太小的寬度設(shè)置不會(huì)導(dǎo)致輸出被截?cái)?，但是?huì)被簡(jiǎn)單地忽略。寬度可以使用下列形式之一指定：一個(gè)正整數(shù)；一個(gè)星號(hào)（*）表示使用下一個(gè)函數(shù)參數(shù)作為寬度；或者一個(gè)形式為*n$的字符串表示使用第n個(gè)函數(shù)參數(shù)作為寬度。

如果寬度來(lái)自于一個(gè)函數(shù)參數(shù)，則參數(shù)在被格式說(shuō)明符的值使用之前就被消耗掉了。如果寬度參數(shù)是負(fù)值，結(jié)果會(huì)在長(zhǎng)度為abs(width)的域中被左對(duì)齊（如果-標(biāo)志被指定）。

type（必需）

格式轉(zhuǎn)換的類型，用于產(chǎn)生格式說(shuō)明符的輸出。支持下面的類型：

  • s將參數(shù)值格式化為一個(gè)簡(jiǎn)單字符串。一個(gè)控制被視為一個(gè)空字符串。

  • I將參數(shù)值視作 SQL 標(biāo)識(shí)符，并在必要時(shí)用雙寫(xiě)引號(hào)包圍它。如果參數(shù)為空，將會(huì)是一個(gè)錯(cuò)誤（等效于quote_ident）。

  • L將參數(shù)值引用為 SQL 文字。一個(gè)空值將被顯示為不帶引號(hào)的字符串NULL（等效于quote_nullable）。

除了以上所述的格式說(shuō)明符之外，要輸出一個(gè)文字形式的%字符，可以使用特殊序列%%。

下面有一些基本的格式轉(zhuǎn)換的例子： 

SELECT format('Hello %s', 'World');
結(jié)果：Hello World

SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
結(jié)果：Testing one, two, three, %

SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
結(jié)果：INSERT INTO "Foo bar" VALUES('O''Reilly')

SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');
結(jié)果：INSERT INTO locations VALUES(E'C:\\Program Files')

下面是使用width域和-標(biāo)志的例子： 

SELECT format('|%10s|', 'foo');
結(jié)果：|       foo|

SELECT format('|%-10s|', 'foo');
結(jié)果：|foo       |

SELECT format('|%*s|', 10, 'foo');
結(jié)果：|       foo|

SELECT format('|%*s|', -10, 'foo');
結(jié)果：|foo       |

SELECT format('|%-*s|', 10, 'foo');
結(jié)果：|foo       |

SELECT format('|%-*s|', -10, 'foo');
結(jié)果：|foo       |

這些例子展示了position域的例子： 

SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
結(jié)果：Testing three, two, one

SELECT format('|%*2$s|', 'foo', 10, 'bar');
結(jié)果：|       bar|

SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
結(jié)果：|       foo|

不同于標(biāo)準(zhǔn)的 C 函數(shù)sprintf，PostgreSQL的format函數(shù)允許將帶有或者不帶有position域的格式說(shuō)明符被混在同一個(gè)格式字符串中。一個(gè)不帶有position域的格式說(shuō)明符總是使用最后一個(gè)被消耗的參數(shù)的下一個(gè)參數(shù)。另外，format函數(shù)不要求所有函數(shù)參數(shù)都被用在格式字符串中。例如： 

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
結(jié)果：Testing three, two, three

對(duì)于安全地構(gòu)造動(dòng)態(tài) SQL 語(yǔ)句，%I和%L格式說(shuō)明符特別有用。參見(jiàn)例 42.1。


以上內(nèi)容是否對(duì)您有幫助：
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號(hào)
微信公眾號(hào)

編程獅公眾號(hào)