閱讀427 返回首頁    go 技術社區[雲棲]

PostgreSQL SQL 語言：函數和操作符

本文檔為PostgreSQL 9.6.0文檔，本轉載已得到原譯者彭煜瑋授權。

常用的邏輯操作符有：

  • AND
  • OR
  • NOT

SQL使用三值的邏輯係統，包括真、假和null，null表示"未知"。觀察下麵的真值表：

image
image


操作符AND和OR是可交換的，也就是說，你可以交換左右操作數而不影響結果。

常見的比較操作符都可用，如Table 9-1所示。

Table 9-1. 比較操作符

image


Note:
!=操作符在分析器階段被轉換成<>。不能把!=和<>操作符實現為做不同的事。

比較操作符可以用於所有可以比較的數據類型。所有比較操作符都是雙目操作符，它們返回boolean類型；類似於1 < 2 < 3的表達式是非法的（因為沒有<操作符可以比較一個布爾值和3）。

如Table 9-2所示，也有一些比較謂詞。它們的行為和操作符很像，但是具有 SQL 標準所要求的特殊語法。

Table 9-2. 比較謂詞

image


BETWEEN謂詞可以簡化範圍測試：


a BETWEEN x AND y

等效於


a >= x AND a <= y

注意BETWEEN認為終點值是包含在範圍內的。 NOT BETWEEN可以做相反比較：


a NOT BETWEEN x AND y

等效於


a < x OR a > y

BETWEEN SYMMETRIC和BETWEEN相似，不過BETWEEN SYMMETRIC不要求AND左邊的參數小於或等於右邊的參數。如果左參數不是小於等於右參數，這兩個參數會自動被交換，這樣總是會應用一個非空範圍。

當有一個輸入為空時，普通的比較操作符會得到空（表示"未知"），而不是真或假。例如，7 = NULL得到空，7 <> NULL也一樣。如果這種行為不合適，可以使用IS [ NOT ] DISTINCT FROM謂詞：


a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b

對於非空輸入，IS DISTINCT FROM和<>操作符一樣。不過，如果兩個輸入都為空，它會返回假。而如果隻有一個輸入為空，它會返回真。類似地，IS NOT DISTINCT FROM對於非空輸入的行為與=相同，但是當兩個輸入都為空時它返回真，並且當隻有一個輸入為空時返回假。因此，這些謂詞實際上把空值當作一種普通數據值而不是"unknown"。

要檢查一個值是否為空，使用下麵的謂詞：


expression IS NULL
expression IS NOT NULL

或者等效，但並不標準的謂詞：


expression ISNULL
expression NOTNULL

不要寫expression = NULL，因為NULL是不"等於"NULL的（控製代表一個未知的值，因此我們無法知道兩個未知的數值是否相等）。

Tip: 有些應用可能要求表達式expression = NULL在expression得出空值時返回真。我們強烈建議這樣的應用修改成遵循 SQL 標準。但是，如果這樣修改不可能完成，那麼我們可以使用配置變量transform_null_equals。如果打開它，PostgreSQL將把x = NULL子句轉換成x IS NULL。

如果expression是行值，那麼當行表達式本身為非空值或者行的所有域為非空時IS NULL為真。由於這種行為，IS NULL和IS NOT NULL並不總是為行值表達式返回反轉的結果，特別是，一個同時包含 NULL 和非空值的域將會對兩種測試都返回假。在某些情況下，寫成row IS DISTINCT FROM NULL或者row IS NOT DISTINCT FROM NULL會更好，它們隻會檢查整個行值是否為空而不需要在行的域上做額外的測試。

布爾值也可以使用下列謂詞進行測試：


boolean_expression IS TRUE
boolean_expression IS NOT TRUE
boolean_expression IS FALSE
boolean_expression IS NOT FALSE
boolean_expression IS UNKNOWN
boolean_expression IS NOT UNKNOWN

這些謂詞將總是返回真或假，從來不返回空值，即使操作數是空也如此。空值輸入被當做邏輯值"未知"。 請注意實際上IS UNKNOWN和IS NOT UNKNOWN分別與IS NULL和IS NOT NULL相同， 隻是輸入表達式必須是布爾類型。

如Table 9-3中所示，也有一些比較相關的函數可用。

Table 9-3. 比較函數

image

PostgreSQL為很多類型提供了數學操作符。對於那些沒有標準數學表達的類型（如日期/時間類型），我們將在後續小節中描述實際的行為。

Table 9-4展示了所有可用的數學操作符。

Table 9-4. 數學操作符

image


按位操作操作符隻能用於整數數據類型，而其它的操作符可以用於全部數字數據類型。按位操作的操作符還可以用於位串類型bit和bit varying， 如Table 9-13所示。

Table 9-5顯示了可用的數學函數。在該表中，dp表示double precision。這些函數中有許多都有多種不同的形式，區別是參數不同。除非特別指明，任何特定形式的函數都返回和它的參數相同的數據類型。 處理double precision數據的函數大多數是在宿主係統的 C 庫基礎上實現的；因此，邊界情況下的準確度和行為是根據宿主係統而變化的。

Table 9-5. 數學函數


10


Table 9-6展示了用於產生隨機數的函數。

Table 9-6. 隨機函數

image


random()返回的值的特征取決於係統實現。 它不適合用於加密應用，如果需要用於加密應用請參考pgcrypto模塊。

最後，Table 9-7顯示了可用的三角函數。所有三角函數都有類型為double precision的參數和返回類型。每一種三角函數都有兩個變體，一個以弧度度量角，另一個以角度度量角。

Table 9-7. 三角函數

image


Note:
另一種使用以角度度量的角的方法是使用早前展示的單位轉換函數radians()和degrees()。不過，使用基於角度的三角函數更好，因為這類方法能避免sind(30)等特殊情況下的舍入偏差。

本節描述了用於檢查和操作字符串值的函數和操作符。在這個環境中的串包括所有類型character、character varying和text的值。除非另外說明，所有下麵列出的函數都可以處理這些類型，不過要小心的是，在使用character類型的時候， 它有自動填充空白的潛在影響。有些函數還可以處理位串類型。

SQL定義了一些字符串函數，它們使用關鍵字，而不是逗號來分隔參數。詳情請見Table 9-8，PostgreSQL也提供了這些函數使用正常函數調用語法的版本（見Table 9-9）。

Note: 由於存在從那些數據類型到text的隱式強製措施，在PostgreSQL 8.3之前，這些函數也可以接受多種非字符串數據類型。這些強製措施在目前的版本中已經被刪除，因為它們常常導致令人驚訝的行為。不過，字符串串接操作符（||）仍然接受非字符串輸入，隻要至少一個輸入是一種字符串類型，如Table 9-8所示。對於其他情況，如果你需要複製之前的行為，可以為text插入一個顯式強製措施。

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


image


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

Table 9-9. 其他字符串函數

111


concat、concat_ws和format函數是可變的，因此可以把要串接或格式化的值作為一個標記了VARIADIC關鍵字的數組進行傳遞（見Section 36.4.5）。數組的元素被當作函數的獨立普通參數一樣處理。如果可變數組參數為 NULL，concat和concat_ws返回 NULL，但format把 NULL 當作一個零元素數組。

還可以參閱Section 9.20中的string_agg。

Table 9-10. 內建轉換

112


4.1 format

函數format根據一個格式字符串產生格式化的輸出，其形式類似於 C 函數sprintf。


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

formatstr是一個格式字符串，它指定了結果應該如何被格式化。格式字符串中的文本被直接複製到結果中，除了使用格式說明符的地方。格式說明符在字符串中扮演著占位符的角色，它定義後續的函數參數如何被格式化及插入到結果中。每一個formatarg參數會被根據其數據類型的常規輸出規則轉換為文本，並接著根據格式說明符被格式化和插入到結果字符串中。

格式說明符由一個%字符開始並且有這樣的形式


%[position][flags][width]type

其中的各組件域是：

position（可選）

一個形式為n$的字符串，其中n是要打印的參數的索引。索引 1 表示formatstr之後的第一個參數。如果position被忽略，默認會使用序列中的下一個參數。

flags（可選）

控製格式說明符的輸出如何被格式化的附加選項。當前唯一支持的標誌是一個負號（-），它將導致格式說明符的輸出會被左對齊（left-justified）。除非width域也被指定，否者這個域不會產生任何效果。

width（可選）

指定用於顯示格式說明符輸出的最小字符數。輸出將被在左部或右部（取決於-標誌）用空格填充以保證充滿該寬度。太小的寬度設置不會導致輸出被截斷，但是會被簡單地忽略。寬度可以使用下列形式之一指定：一個正整數；一個星號（*）表示使用下一個函數參數作為寬度；或者一個形式為*n$的字符串表示使用第n個函數參數作為寬度。

如果寬度來自於一個函數參數，則參數在被格式說明符的值使用之前就被消耗掉了。如果寬度參數是負值，結果會在長度為abs(width)的域中被左對齊（如果-標誌被指定）。

type（必需）

格式轉換的類型，用於產生格式說明符的輸出。支持下麵的類型：

s將參數值格式化為一個簡單字符串。一個控製被視為一個空字符串。

I將參數值視作 SQL 標識符，並在必要時用雙寫引號包圍它。如果參數為空，將會是一個錯誤（等效於quote_ident）。

L將參數值引用為 SQL 文字。一個空值將被顯示為不帶引號的字符串NULL（等效於quote_nullable）。

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

下麵有一些基本的格式轉換的例子：


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

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

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

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

下麵是使用width域和-標誌的例子：


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

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

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

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

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

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

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


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

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

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

不同於標準的 C 函數sprintf，PostgreSQL的format函數允許將帶有或者不帶有position域的格式說明符被混在同一個格式字符串中。一個不帶有position域的格式說明符總是使用最後一個被消耗的參數的下一個參數。另外，format函數不要求所有函數參數都被用在格式字符串中。例如：


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

本節描述那些檢查和操作類型為bytea的值的函數和操作符。

SQL定義了一些使用關鍵字而不是逗號來分割參數的串函數。詳情請見Table 9-11。PostgreSQL也提供了這些函數使用常規函數調用語法的版本（參閱Table 9-12）。

Note: 本頁中顯示的示例結果假設服務器參數bytea_output被設置為escape（傳統PostgreSQL格式）。

Table 9-11. SQL二進製串函數和操作符

image

還有一些二進製串處理函數可以使用，在Table 9-12列出。 其中有一些是在內部使用，用於實現Table 9-11列出的 SQL 標準串函數。

Table 9-12. 其他二進製串函數

image


get_byte和set_byte把一個二進製串中的一個字節計數為字節 0。get_bit和set_bit在每一個字節中從右邊起計數位；例如位 0 是第一個字節的最低有效位，而位 15 是第二個字節的最高有效位。

本節描述用於檢查和操作位串的函數和操作符，也就是操作類型為bit和bit varying的值的函數和操作符。除了常用的比較操作符之外，還可以使用Table 9-13裏顯示的操作符。&、|和#的位串操作數必須等長。在移位的時候，保留原始的位串的的長度，如例子所示。

Table 9-13. 位串操作符

image


下麵的SQL標準函數除了可以用於字符串之外，也可以用於位串： length、 bit_length、 octet_length、 position、 substring、 overlay。

下麵的函數除了可以用於二進製串之外，也可以用於位串： get_bit、 set_bit。 當使用於一個位串時，這些函數將串的第一（最左）位計數為位 0。

另外，我們可以在整數和bit之間來回轉換。一些例子：


44::bit(10)                    0000101100
44::bit(3)                     100
cast(-44 as bit(12))           111111010100
'1110'::bit(4)::integer        14

請注意，如果隻是轉換為"bit"，意思是轉換成bit(1)，因此隻會轉換整數的最低有效位。

Note:
把一個整數轉換成bit(n)將拷貝整數的最右邊的n位。 把一個整數轉換成比整數本身長的位串，就會在最左邊擴展符號。

PostgreSQL提供了三種獨立的實現模式匹配的方法：SQL LIKE操作符、更近一些的SIMILAR TO操作符（SQL:1999 裏添加進來的）和POSIX-風格的正則表達式。除了這些基本的"這個串匹配這個模式嗎？"操作符外，還有一些函數可用於提取或替換匹配子串並在匹配位置分離一個串。

Tip: 如果你的模式匹配的要求超出了這些，請考慮用 Perl 或 Tcl 寫一個用戶定義的函數。

Caution

雖然大部分的正則表達式搜索都能被很快地執行，但是正則表達式仍可能被 人為地弄成需要任意長的時間和任意量的內存進行處理。要當心從不懷好意 的來源接受正則表達式搜索模式。如果必須這樣做，建議加上語句超時限製。

使用SIMILAR TO模式的搜索具有同樣的安全性危險， 因為SIMILAR TO提供了很多和 POSIX-風格正則表達式相同的能力。

LIKE搜索比其他兩種選項簡單得多，因此在使用 不懷好意的模式來源時要更安全些。

9.7.1. LIKE


string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]

如果該string匹配了提供的pattern，那麼LIKE表達式返回真（和預期的一樣，如果LIKE返回真，那麼NOT LIKE表達式返回假， 反之亦然。一個等效的表達式是NOT (string LIKE pattern)）。

如果pattern不包含百分號或者下劃線，那麼該模式隻代表它本身的串；這時候LIKE的行為就象等號操作符。在pattern裏的下劃線 （_）代表（匹配）任何單個字符； 而一個百分號（%）匹配任何零或更多個字符的序列。

一些例子：


'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false

LIKE模式匹配總是覆蓋整個串。因此，要匹配在串內任何位置的序列，該模式必須以百分號開頭和結尾。

要匹配文本的下劃線或者百分號，而不是匹配其它字符， 在pattern裏相應的字符必須 前導逃逸字符。缺省的逃逸字符是反斜線，但是你可以用ESCAPE子句指定一個不同的逃逸字符。 要匹配逃逸字符本身，寫兩個逃逸字符。

Note:
如果你關掉了standard_conforming_strings，你在文串常量中寫的任何反斜線都需要被雙寫。

請注意反斜線在串文本裏已經有特殊含義了，所以如果你寫一個 包含反斜線的模式常量，那你就要在 SQL 語句裏寫兩個反斜線。 因此，寫一個匹配單個反斜線的模式實際上要在語句裏寫四個反斜線。 你可以通過用 ESCAPE 選擇一個不同的逃逸字符 來避免這樣；這樣反斜線就不再是 LIKE 的特殊字符了。 但仍然是字符文本分析器的特殊字符，所以你還是需要兩個反斜線。) 我們也可以通過寫ESCAPE ''的方式不選擇逃逸字符，這樣可以有效地禁用逃逸機製，但是沒有辦法關閉下劃線和百分號在模式中的特殊含義。

關鍵字ILIKE可以用於替換LIKE， 它令該匹配根據活動區域成為大小寫無關。這個不屬於SQL標準而是一個PostgreSQL擴展。

操作符~~ 等效於LIKE， 而~~ *對應ILIKE。 還有 !~~ 和!~~ *操作符分別代表NOT LIKE和NOT ILIKE。所有這些操作符都是PostgreSQL特有的。

9.7.2. SIMILAR TO正則表達式


string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]

SIMILAR TO操作符根據自己的模式是否匹配給定串而返回真或者假。 它和LIKE非常類似，隻不過它使用 SQL 標準定義的正則表達式理解模式。 SQL 正則表達式是在LIKE標記和普通的正則表達式標記的奇怪的雜交。

類似LIKE，SIMILAR TO操作符隻有在它的模式匹配整個串的時候才能成功；這一點和普通的 正則表達式的行為不同，在普通的正則表達式裏，模式匹配串的任意部分。 和LIKE類似的地方還有，SIMILAR TO使用_和%作為分別代表任意單個字符和任意串的通配符（這些可以比得上 POSIX 正則表達式裏的.和.*）。

除了這些從LIKE借用的功能之外，SIMILAR TO支持下麵這些從 POSIX 正則表達式借用的 模式匹配元字符：

  • |表示選擇（兩個候選之一）。
  • *表示重複前麵的項零次或更多次。
  • +表示重複前麵的項一次或更多次。
  • ?表示重複前麵的項零次或一次。
  • {m}表示重複前麵的項剛好m次。
  • {m,}表示重複前麵的項m次或更多次。
  • {m,n}表示重複前麵的項至少m次並且不超過n次。
  • 可以使用圓括號()把多個項組合成一個邏輯項。
  • 一個方括號表達式[...]聲明一個字符類，就像 POSIX 正則表達式一樣。

注意點號（.）不是SIMILAR TO的一個元字符。

和LIKE一樣，反斜線禁用所有這些元字符的特殊含義；當然我們也可以用ESCAPE指定一個不同的逃逸字符。

一些例子：


'abc' SIMILAR TO 'abc'      true
'abc' SIMILAR TO 'a'        false
'abc' SIMILAR TO '%(b|d)%'  true
'abc' SIMILAR TO '(b|c)%'   false

帶三個參數的substring，即substring(string from pattern for escape-character)，提供了抽取一個匹配 SQL 正則表達式的子串的方法。和SIMILAR TO一樣，聲明的模式必須匹配整個數據串，否則函數失敗並返回空值。為了標識在成功的時候應該返回的模式部分，模式 必須包含逃逸字符的兩次出現，並且後麵要跟上雙引號（"）。匹配這兩個標記之間的模式的文本將被返回。

一些例子，使用#"定界返回串：


substring('foobar' from '%#"o_b#"%' for '#')   oob
substring('foobar' from '#"o_b#"%' for '#')    NULL

9.7.3. POSIX正則表達式

Table 9-14列出了所有可用於 POSIX 正則表達式模式匹配的操作符。

Table 9-14. 正則表達式匹配操作符


image


POSIX正則表達式提供了比LIKE和SIMILAR TO操作符更強大的含義。許多 Unix 工具，例如egrep、sed或awk使用一種與我們這裏描述的類似的模式匹配語言。

正則表達式是一個字符序列，它是定義一個串集合 （一個正則集）的縮寫。 如果一個串是正則表達式描述的正則集中的一員時， 我們就說這個串匹配該正則表達式。 和LIKE一樣，模式字符準確地匹配串字符， 除非在正則表達式語言裏有特殊字符 — 不過正則表達式用的 特殊字符和LIKE用的不同。 和LIKE模式不一樣的是，正則表達式允許匹配串裏的任何位置，除非該正則表達式顯式地掛接在串的開頭或者結尾。

一些例子：


'abc' ~ 'abc'    true
'abc' ~ '^a'     true
'abc' ~ '(b|d)'  true
'abc' ~ '^(b|c)' false

POSIX模式語言的詳細描述見下文。

帶兩個參數的substring函數，即substring(string from pattern)，提供了抽取一個匹配 POSIX 正則表達式模式的子串的方法。如果沒有匹配它返回空值，否則就是文本中匹配模式的那部分。 但是如果該模式包含任何圓括號，那麼將返回匹配第一對子表達式（對應第一個左圓括號的） 的文本。如果你想在表達式裏使用圓括號而又不想導致這個例外，那麼你可以在整個表達式外邊放上一對圓括號。 如果你需要在想抽取的子表達式前有圓括號，參閱後文描述的非捕獲性圓括號。

一些例子：


substring('foobar' from 'o.b')     oob
substring('foobar' from 'o(.)b')   o

regexp_replace函數提供了將匹配 POSIX 正則表達式模式的子串替換為新文本的功能。 它的語法是 regexp_replace(source, pattern, replacement [, flags ])。 如果沒有匹配pattern，那麼返回不加修改的source串。 如果有匹配，則返回的source串裏麵的匹配子串將被replacement串替換掉。replacement串可以包含\n， 其中\n是 1 到 9， 表明源串裏匹配模式裏第n個圓括號子表達式的子串應該被插入， 並且它可以包含&表示應該插入匹配整個模式的子串。如果你需要放一個文字形式的反斜線在替換文本裏，那麼寫\。flags參數是一個可選的文本串，它包含另個或更多單字母標誌，這些標誌可以改變函數的行為。標誌i指定大小寫無關的匹配，而標誌g指定替換每一個匹配的子串而不僅僅是第一個。支持的標誌（但不是g）在Table 9-22中描述。

一些例子：


regexp_replace('foobarbaz', 'b..', 'X')
                                   fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
                                   fooXX
regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g')
                                   fooXarYXazY

regexp_matches函數返回一個文本數組，該數組由匹配一個 POSIX 正則表達式模式得到的所有被捕獲子串構成。其語法是regexp_matches(string, pattern [, flags ])。該函數可以不返回任何行、返回一行或者返回多行（見下文的g）。如果pattern不匹配，該函數不返回行。如果模式不包含圓括號子表達式，則每一個被返回的行都是一個單一元素的文本數組，其中包括匹配整個模式的子串。如果模式包含圓括號子表達式，該函數返回一個文本數組，它的第n個元素是匹配模式的第n個圓括號子表達式的子串（"非捕獲"圓括號不計算在內，詳見下文）。flags參數是一個可選的文本字符串，它包含零個或更多個單字母標誌，它們可以改變函數的行為。標誌g讓函數尋找串中的每一個匹配，而不僅僅是第一個，並且為每一個這樣的匹配返回一行。支持的標誌（但不是g）在Table 9-22中描述。

一些例子：


SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
 regexp_matches 
----------------
 {bar,beque}
(1 row)

SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
 regexp_matches 
----------------
 {bar,beque}
 {bazil,barf}
(2 rows)

SELECT regexp_matches('foobarbequebaz', 'barbeque');
 regexp_matches 
----------------
 {barbeque}
(1 row)

也可以強製regexp_matches()通過使用一個子選擇來總是返回一行。當你希望所有行都被返回（甚至是不能匹配的行）時，把它用在一個SELECT目標列表中會特別有用：


SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;

regexp_split_to_table把一個 POSIX 正則表達式模式當作一個定界符來分離一個串。它的語法形式是regexp_split_to_table(string, pattern [, flags ])。如果沒有與pattern的匹配，該函數返回string。如果有至少有一個匹配，對每一個匹配它都返回從上一個匹配的末尾（或者串的開頭）到這次匹配開頭之間的文本。當沒有更多匹配時，它返回從上一次匹配的末尾到串末尾之間的文本。flags參數是一個可選的文本串，它包含零個或更多單字母標誌，這些標識可以改變該函數的行為。regexp_split_to_table能支持的標誌在Table 9-22中描述。

regexp_split_to_array函數的行為和regexp_split_to_table相同，不過regexp_split_to_array會把它的結果以一個text數組的形式返回。它的語法是regexp_split_to_array(string, pattern [, flags ])。這些參數和regexp_split_to_table的相同。

一些例子：



SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', E'\\s+') AS foo;
  foo   
-------
 the    
 quick  
 brown  
 fox    
 jumps 
 over   
 the    
 lazy   
 dog    
(9 rows)

SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', E'\\s+');
              regexp_split_to_array             
-----------------------------------------------
 {the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 row)

SELECT foo FROM regexp_split_to_table('the quick brown fox', E'\\s*') AS foo;
 foo 
-----
 t         
 h         
 e         
 q         
 u         
 i         
 c         
 k         
 b         
 r         
 o         
 w         
 n         
 f         
 o         
 x         
(16 rows)

正如上一個例子所示，正則表達式分離函數會忽略零長度的匹配，這種匹配發生在串的開頭或結尾或者正好發生在前一個匹配之後。這和正則表達式匹配的嚴格定義是相悖的，後者由regexp_matches實現，但是通常前者是實際中最常用的行為。其他軟件係統如Perl也使用相似的定義。

7.3.1. 正則表達式細節

PostgreSQL的正則表達式是使用 Henry Spencer 寫的一個包來實現的。下麵的正則表達式的大部分描述都是從他的手冊頁中逐字拷貝過來的。

正則表達式（RE），在POSIX 1003.2 中定義， 它有兩種形式：擴展的RE或者是ERE（大概地說就是那些在egrep裏的）， 基本的RE或者是BRE（大概地說就是那些在ed裏的）。PostgreSQL支持兩種形式，並且還實現了一些POSIX標準中沒有但是在類似 Perl 或者 Tcl 這樣的語言中得到廣泛應用的一些擴展。使用了那些非POSIX擴展的RE叫高級RE， 或者本文檔裏說的ARE。ARE 幾乎完全是 ERE 的超集，但是 BRE 有幾個符號上的不兼容（以及更多的限製）。我們首先描述 ARE 和 ERE 形式， 描述那些隻適用於 ARE 的特性，然後描述 BRE 的區別是什麼。

Note:
PostgreSQL初始時總是推測一個正則表達式遵循 ARE 規則。但是，可以通過為 RE 模式預置一個embedded option來選擇限製更多的 ERE 或 BRE 規則，如Section 9.7.3.4中所述。這對為期望準確的POSIX 1003.2 規則的應用提供兼容性很有用。

一個正則表達式被定義為一個或更多分支，它們之間被|分隔。隻要能匹配其中一個分支的東西都能匹配正則表達式。

一個分支是一個或多個量化原子或者約束連接而成。一個原子匹配第一個，然後後麵的原子匹配第二個， 以此類推；一個空分支匹配空串。

一個量化原子是一個原子， 後麵可能跟著一個量詞。沒有量詞的時候，它匹配一個原子， 有量詞的時候，它可以匹配若幹個原子。一個原子可以是在Table 9-15裏麵顯示的任何可能。 可能的量詞和它們的含義在Table 9-16裏顯示。

一個約束匹配一個空串，但隻是在滿足特定條件下才匹配。 約束可以在能夠使用原子的地方使用，隻是它不能跟著量詞。簡單的約束在Table 9-17裏顯示； 更多的約束稍後描述。

Table 9-15. 正則表達式原子


image


RE 不能以反斜線（\）結尾。

Note:
如果你關掉了standard_conforming_strings，任何你寫在文字串常量中的反斜線都需要被雙寫。

Table 9-16. 正則表達式量詞


image


使用{...}的形式被稱作範圍。 一個範圍內的數字m和n都是無符號十進製整數， 允許的數值從 0 到 255（包含）。

非貪婪的量詞（隻在 ARE 中可用）匹配對應的正常 （貪婪）模式，區別是它尋找最少的匹配，而不是最多的匹配。詳見Section 9.7.3.5。

Note: 一個量詞不能緊跟在另外一個量詞後麵，例如**是非法的。量詞不能作為表達式或者子表達式的開頭，也不能跟在^或者|後麵。

Table 9-17. 正則表達式約束


image


ookahead 和 lookbehind 約束不能包含後引用 （參閱Section 7.3.3），並且其中的所有圓括號 都被認為是非捕獲的。

7.3.2. 方括號表達式

方括號表達式是一個包圍在[]中的字符列表。它通常匹配列表中的任意單個字符（但見下文）。 如果列表以^開頭，它匹配任意單個不在該列表參與部分中的字符。如果該列表中兩個字符用-隔開， 那它就是那兩個字符（包括在內）之間的所有字符範圍的縮寫，例如，在ASCII中[0-9]匹配任何十進製數字。兩個範圍共享一個端點是非法的，例如，a-c-e。範圍與字符集關係密切， 可移植的程序應該避免依靠它們。

想在列表中包含文本]，可以讓它做列表的首字符（如果使用了^，需要放在其後）。 想在列表中包含文本-，可以讓它做列表的首字符或者尾字符，或者一個範圍的第二個端點。 想在列表中把文本-當做範圍的起點， 把它用[.和.]包圍起來，這樣它就成為一個排序元素（見下文）。 除了這些字符本身、一些用[的組合（見下段）以及逃逸（隻在 ARE 中有效）以外，所有其它特殊字符 在方括號表達式裏都失去它們的特殊含義。特別是，在 ERE 和 BRE 規則下\不是特殊的， 但在 ARE 裏，它是特殊的（引入一個逃逸）。

在一個方括號表達式裏，一個排序元素（一個字符、一個被當做一個單一字符排序的多字符序列或者一個表示上麵兩種情況的排序序列名稱） 包含在[.和.]裏麵的時候表示該排序元素的字符序列。該序列被當做該方括號列表 的一個單一元素。這允許一個包含多字符排序元素的方括號表達式去匹配多於一個字符，例如，如果排序序列包含一個ch排序元素， 那麼 RE [[.ch.]]*c匹配chchcc的頭五個字符。

Note:
PostgreSQL當前不支持多字符排序元素。這些信息描述了將來可能有的行為。

在方括號表達式裏，包圍在[=和=]裏的排序元素是一個等價類， 代表等效於那一個的所有排序元素的字符序列，包括它本身（如果沒有其它等效排序元素，那麼就好象封裝定界符是[.和 .]）。例如，如果o和^是一個等價類的成員，那麼[[=o=]]、[[=^=]]和[o^]都是同義的。一個等價類不能是一個範圍的端點。

在方括號表達式裏，在[:和:]裏麵封裝的字符類的名字代表屬於該類的所有字符的列表。 標準的字符類名字是：alnum、 alpha、blank、 cntrl、digit、 graph、lower、 print、punct、 space、upper、 xdigit。 它們代表在ctype中定義的字符類。 一個區域可以會提供其他的類。字符類不能用做一個範圍的端點。

方括號表達式裏有兩個特例：方括號表達式[[:<:]]和[[:>:]]是約束，分別匹配一個單詞開頭和結束的空串。 單詞定義為一個單詞字符序列，前麵和後麵都沒有其它單詞字符。單詞字符是一個alnum字符（和ctype中定義的一樣） 或者一個下劃線。這是一個擴展，兼容POSIX 1003.2， 但那裏麵並沒有說明， 而且在準備移植到其他係統裏去的軟件裏一定要小心使用。通常下文描述的約束逃逸更好些（它們並非更標準，但是更容易鍵入）。

7.3.3. 正則表達式逃逸

逃逸是以\開頭，後麵跟著一個字母數字字符得特殊序列。 逃逸有好幾種變體：字符項、類縮寫、約束逃逸以及後引用。在 ARE 裏， 如果一個\後麵跟著一個字母數字，但是並未組成一個合法的逃逸， 那麼它是非法的。在 ERE 中沒有逃逸：在方括號表達式之外，一個後麵跟著字母數字字符的\隻是表示該字符是一個普通的字符，而且在一個方括號表達式裏，\是一個普通的字符（後者實際上在 ERE 和 ARE 不兼容）。

字符項逃逸用於便於我們在 RE 中聲明那些不可打印的或其他習慣的字符。它們顯示在Table 9-18中。

類縮寫逃逸用來提供一些常用的字符類縮寫。它們顯示在Table 9-19中。

約束逃逸是一個約束，如果滿足特定的條件，它匹配該空串。它們顯示在Table 9-20中。

後引用（\n）匹配數字\n指定的被前麵的圓括號子表達式匹配的同一個串 （參閱Table 9-21）。例如， ([bc])\1匹配bb或者cc， 但是不匹配bc或者cb。RE 中子表達式必須完全在後引用前麵。子表達式以它們的先導圓括號的順序編號。非捕獲圓括號並不定義子表達式。

Table 9-18. 正則表達式字符項逃逸


image


十六進製位是0-9、a-f和A-F。八進製位是0-7。

指定 ASCII 範圍（0-127）之外的值的數字字符項轉義的含義取決於數據庫編碼。 當編碼是 UTF-8 時，轉義值等價於 Unicode 代碼點，例如 \u1234表示字符U+1234。對於其他多字節編碼， 字符項轉義通常隻是指定該字符的字節值的串接。如果該轉義值不對應數據庫編碼 中的任何合法字符，將不會發生錯誤，但是它不會匹配任何數據。

字符項逃逸總是被當作普通字符。例如，\135是 ASCII 中的]， 但\135並不終止一個方括號表達式。

Table 9-19. 正則表達式類縮寫逃逸


image


在方括號表達式裏，\d、\s和\w會失去它們的外層方括號，而\D、\S和 \W是非法的（也就是說，例如[a-c\d]等效於[a-c[:digit:]]。同樣[a-c\D]等效於 [a-c^[:digit:]]的，也是非法的）。

Table 9-20. 正則表達式約束逃逸


image


一個詞被定義成在上麵[[:<:]]和[[:>:]]中的聲明。在方括號表達式裏，約束逃逸是非法的。

Table 9-21. 正則表達式後引用

image


Note: 在八進製字符項逃逸和後引用之間有一個曆史繼承的歧義存在，這個歧義是 通過下麵的啟發式規則解決的，像上麵描述地那樣。前導零總是表示這是一個八進製逃逸。 而單個非零數字，如果沒有跟著任何其它位，那麼總是被認為後引用。 一個多位的非零開頭的序列也被認為是後引用，隻要它出現在合適的子表達式後麵 （也就是說，在後引用的合法範圍中的數），否則就被認為是一個八進製。

7.3.4. 正則表達式元語法

除了上麵描述的主要語法之外，還有幾種特殊形式和雜項語法。

如果一個 RE 以***:開頭，那麼剩下的 RE 都被當作 ARE（這在PostgreSQL中通常是無效的，因為 RE 被假定為 ARE，但是如果 ERE 或 BRE 模式通過flags參數被指定為一個正則表達式函數時，它確實能產生效果）。如果一個 RE 以***=開頭， 那麼剩下的 RE 被當作一個文本串，所有的字符都被認為是一個普通字符。

一個 ARE 可以以嵌入選項開頭：一個序列(?xyz)（這裏的xyz是一個或多個字母字符）聲明影響剩餘 RE 的選項。 這些選項覆蓋任何前麵判斷的選項 — 特別地，它們可以覆蓋一個正則表達式操作符隱含的大小寫敏感的行為，或者覆蓋flags參數中的正則表達式函數。可用的選項字母在Table 9-22中顯示。注意這些同樣的選項字母也被用在正則表達式函數的flags參數中。

Table 9-22. ARE 嵌入選項字母

image


嵌入選項在)終止序列時發生作用。它們隻在 ARE 的開始處起作用 （在任何可能存在的***:控製器後麵）。

除了通常的（緊）RE 語法（這種情況下所有字符都有效）， 還有一種擴展語法，可以通過聲明嵌入的x選項獲得。在擴展語法裏，RE 中的空白字符被忽略，就像那些在#和其後的新行（或 RE 的末尾）之間的字符一樣。這樣就允許我們給一個複雜的 RE 分段和注釋。不過這個基本規則有三種例外：

  • 空白字符或前置了\的#將被保留
  • 方括號表達式裏的空白或者#將被保留
  • 在多字符符號裏麵不能出現空白和注釋，例如(?:

為了這個目的，空白是空格、製表符、新行和任何屬於空白字符類的字符。

最後，在 ARE 裏，方括號表達式外麵，序列(?#ttt)（其中ttt是任意不包含一個))的文本）是一個注釋， 它被完全忽略。同樣，這樣的東西是不允許出現在多字符符號的字符中間的，例如 (?:。這種注釋更像是一種曆史產物而不是一種有用的設施，並且它們的使用已經被廢棄；請使用擴展語法來替代。

如果聲明了一個初始的***=控製器，那麼所有這些元語法擴展都不能使用，因為這樣表示把用戶輸入當作一個文字串而不是 RE 對待。

7.3.5. 正則表達式匹配規則

在 RE 可以在給定串中匹配多於一個子串的情況下， RE 匹配串中最靠前的那個子串。如果 RE 可以匹配在那個位置開始 的多個子串，要麼是取最長的子串，要麼是最短的，具體哪種， 取決於 RE 是貪婪的還是非貪婪的。

一個 RE 是否貪婪取決於下麵規則：

  • 大多數原子以及所有約束，都沒有貪婪屬性（因為它們畢竟無法匹配個數變化的文本）。
  • 在一個 RE 周圍加上圓括號並不會改變其貪婪性。
  • 帶一個固定重複次數量詞 （{m}或者{m}?） 的量化原子和原子自身具有同樣的貪婪性（可能是沒有）。
  • 一個帶其他普通的量詞（包括{m,n}中m等於n的情況）的量化原子是貪婪的（首選最長匹配）。
  • 一個帶非貪婪量詞（包括{m,n}?中m等於 n的情況）的量化原子是非貪婪的（首選最短匹配）。
  • 一個分支 — 也就是說，一個沒有頂級|操作符的 RE — 和它裏麵的第一個有貪婪屬性的量化原子有著同樣的貪婪性。
  • 一個由|操作符連接起來的兩個或者更多分支組成的 RE 總是貪婪的。

上麵的規則所描述的貪婪屬性不僅僅適用於獨立的量化原子， 而且也適用於包含量化原子的分支和整個 RE。這裏的意思是， 匹配是按照分支或者整個 RE 作為一個整體匹配最長或者最短的可能子串。 一旦整個匹配的長度確定，那麼匹配任意特定子表達式的部分就基於該子表達式的貪婪屬性進行判斷，在 RE 裏麵靠前的子表達式的優先級高於靠後的子表達式。

一個相應的例子：


SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');
結果：123
SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
結果：1

在第一個例子裏，RE 作為整體是貪婪的，因為Y*是貪婪的。它可以匹配從Y開始的東西，並且它匹配從這個位置開始的最長的串， 也就是，Y123。輸出是這裏的圓括號包圍的部分，或者說是123。在第二個例子裏， RE 總體上是一個非貪婪的 RE，因為Y*?是非貪婪的。它可以匹配從Y開始的最短的子串，也就是說Y1。子表達式[0-9]{1,3}是貪婪的，但是它不能修改總體匹配長度的決定； 因此它被迫隻匹配1。

簡而言之，如果一個 RE 同時包含貪婪和非貪婪的子表達式，那麼總的匹配長度要麼是盡可能長，要麼是盡可能短，這取決於給整個 RE 賦予的屬性。給子表達式賦予的屬性隻影響在這個匹配裏，各個子表達式之間相互允許"吃掉"的多少。

量詞{1,1}和{1,1}?可以分別用於在一個子表達式 或者整個 RE 上強製貪婪或者非貪婪。當需要整個 RE 具有不同於從其元素中 推導出的貪婪屬性時，這很有用。例如，假設我們嚐試將一個包含一些數字的 字符串分隔成數字以及在它們之前和之後的部分，我們可能會嚐試這樣做：


SELECT regexp_matches('abc01234xyz', '(.*)(\d+)(.*)');
Result: {abc0123,4,xyz}

這不會有用：第一個.*是貪婪的，因此它會"吃掉" 盡可能多的字符而留下\d+去匹配在最後一個可能位置上的最 後一個數字。我們可能會通過讓它變成非貪婪來修複：


SELECT regexp_matches('abc01234xyz', '(.*?)(\d+)(.*)');
Result: {abc,0,""}

這也不會有用：因為現在 RE 作為整體來說是非貪婪的，因此它會盡快結束 全部的匹配。我們可以通過強製 RE 整體是貪婪的來得到我們想要的：


SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
Result: {abc,01234,xyz}

獨立於 RE 的組件的貪婪性之外控製 RE 的整體貪婪性為處理變長模式提供了 很大的靈活性。

在決定更長或者更短的匹配時，匹配長度是以字符衡量的，而不是排序元素。一個空串會被認為比什麼都不匹配長。例如：bb*匹配abbbc的中間三個字符；(week|wee)(night|knights)匹配weeknights的所有十個字符； 而(.*).*匹配 abc的時候，圓括號包圍的子表達式匹配所有三個字符；當(a*)*被拿來匹配bc時，整個 RE 和圓括號 子表達式都匹配一個空串。

如果聲明了大小寫無關的匹配，那麼效果就好像所有大小寫區別在字母表中消失了。如果在多個情況中一個字母以一個普通字符的形式出現在方括號表達式外麵，那麼它實際上被轉換成 一個包含大小寫的方括號表達式，也就是說，x 變成 [xX]。 如果它出現在一個方括號表達式裏麵，那麼它的所有大小寫的同族都被加入 方括號表達式中，也就是說，x變成[xX]。當它出現在一個方括號表達式內時，它的所有大小寫副本都被加入到方括號表達式中，例如， [x]會變成[xX]，而[^x]會變成[^xX]。

如果指定了新行敏感的匹配，.和使用^的方括號表達式 將永遠不會匹配新行字符（這樣，匹配就絕對不會跨越新行，除非 RE 顯式地安排了這樣的情況）並且^和$除了分別匹配串開頭和結尾之外，還將分別匹配新行後麵和前麵的空串。但是 ARE 逃逸\A和\Z仍然隻匹配串的開頭和結尾。

如果指定了部分新行敏感的匹配，那麼它影響.和方括號表達式， 這個時候和新行敏感的匹配一樣，但是不影響^和$。

如果指定了逆新行敏感匹配，那麼它影響^和$，其作用和在新行敏感的匹配裏一樣，但是不影響.和方括號表達式。這個並不是很有用，隻是為了滿足對稱性而提供的。

7.3.6. 限製和兼容性

在這個實現裏，對 RE 的長度沒有特別的限製。但是，那些希望高移植性的程序應該避免使用長度超過 256 字節的 RE，因為 POSIX 兼容 的實現可以拒絕接受這樣的 RE。

ARE 實際上和 POSIX ERE 不兼容的唯一的特性是在方括號表達式裏\並不失去它特殊的含義。所有其它 ARE 特性都使用在 POSIX ERE 裏麵是非法或者是未定義、未聲明效果的語法；指示器的***就是在 POSIX 的 BRE 和 ERE 之外的語法。

許多 ARE 擴展都是從 Perl 那裏借來的（但是有些被做了修改來清理它們），以及一些 Perl 裏沒有出現的擴展。要注意的不兼容性包括\b、\B、對結尾的新行缺乏特別的處理、對那些被新行敏感匹配的東西附加的補齊方括號表達式、在 lookahead/lookbehind 約束裏對圓括號和後引用的限製以及最長/最短 匹配（而不是第一匹配）的語義。

PostgreSQL 7.4 之前的版本中識別的 ARE 和 ERE 語法存在兩個非常明顯的不兼容：

  • 在 ARE 中，後麵跟著一個字母數字字符的\要麼是一個逃逸要麼是一個錯誤， 但是在以前的版本裏，它隻是寫該字母數字字符的另外一種方法。這個應該不是什麼問題， 因為在以前的版本裏沒有什麼理由會讓我們寫這樣的序列。
  • 在 ARE 裏，\在[]裏還是一個特殊字符， 因此在方括號表達式裏的一個文本\必須被寫成\。

7.3.7. 基本正則表達式

BRE 在幾個方麵和 ERE 不太一樣。在 BRE 中，|、+和?都是普通字符並且沒有與它們功能等價的東西。範圍的定界符是\{和\}， 因為 {和}本身是普通字符。嵌套的子表達式的圓括號是\(和\)，因為(和)自身是普通字符。除非在 RE 開頭或者是圓括號子表達式開頭，^都是一個普通字符。 除非在 RE 結尾或者是圓括號子表達式的結尾，$是一個普通字符。如果*出現在 RE 開頭或者是圓括號封裝的子表達式開頭 （前麵可能有^），那麼它是個普通字符。最後，可以用單數字的後引用，\<和\>分別是[[:<:]]和[[:>:]]的同義詞；在 BRE 中沒有其它可用的逃逸。

PostgreSQL格式化函數提供一套強大的工具用於把各種數據類型 （日期/時間、整數、浮點、數字） 轉換成格式化的字符串以及反過來從格式化的字符串轉換成 指定的數據類型。Table 9-23列出了這些函數。這些函數都遵循一個公共的調用規範： 第一個參數是待格式化的值，而第二個是一個定義輸出或輸入格式的模板。

Table 9-23. 格式化函數


image

Note:
還有一個單一參數的to_timestamp函數，請見Table 9-30。

在一個to_char輸出模板串中，一些特定的模式可以被識別並且被替換成基於給定值的被恰當地格式化的數據。任何不屬於模板模式的文本都簡單地照字麵拷貝。同樣，在一個輸入 模板串裏（對其他函數），模板模式標識由輸入數據串提供的值。

Table 9-24展示了可以用於格式化日期和時間值的模版。

Table 9-24. 用於日期/時間格式化的模板模式


113

修飾語可以被應用於模板模式來修改它們的行為。例如，FMMonth就是帶著FM修飾語的Month模式。Table 9-25展示了可用於日期/時間格式化的修飾語模式。

Table 9-25. 用於日期/時間格式化的模板模式修飾語


image

日期/時間格式化的使用須知：

  • FM抑製前導的零或尾隨的空白， 否則會把它們增加到輸入從而把一個模式的輸出變成固定寬度。在PostgreSQL中，FM隻修改下一個聲明，而在 Oracle 中，FM影響所有隨後的聲明，並且重複的FM修飾語將觸發填充模式開和關。
  • TM不包括結尾空白。to_timestamp和to_date會忽略TM修飾語。
  • 如果沒有使用FX選項，to_timestamp和to_date會跳過輸入字符串中的多個空白。例如，to_timestamp('2000 JUN', 'YYYY MON')是正確的，但to_timestamp('2000 JUN', 'FXYYYY MON')會返回一個錯誤，因為to_timestamp隻期望一個空白。FX必須被指定為模板中的第一個項。
  • to_timestamp和to_date的存在是為了 處理無法被簡單轉換的輸入格式。這些函數會從字麵上解釋輸入，並做一點點錯誤檢查。當 它們產生有效輸出時，該轉換可能得到意料之外的結果。例如，這些函數的輸入沒有被限製 在正常的範圍內，因此to_date('20096040','YYYYMMDD')會返回 2014-01-17而不是報錯。而造型不會有這樣的行為。
  • 在to_char模板裏可以有普通文本，並且它們會被照字麵輸出。你可以把一個子串放到雙引號裏強迫它被解釋成一個文本，即使它裏麵包含模式關鍵字也如此。例如，在 '"Hello Year "YYYY'中，YYYY將被年份數據代替，但是Year中單獨的Y不會。在to_date、to_number和to_timestamp中，雙引號字符串會跳過包含在字符串中字符個數的字符，例如"XX"跳過兩個輸入字符。
  • 如果你想在輸出裏有雙引號，那麼你必須在它們前麵放反斜線，例如 '\"YYYY Month\"'。
  • 如果年份格式聲明少於四位（如YYY）並且提供的年份少於四位，年份將被調整為最接近於 2020 年，例如95會變成 1995。
  • 在處理長於 4 位的年份時，從字串向timestamp或者date的YYYY轉換有一個限製。你必須在YYYY後麵使用一些非數字字符或者模板， 否則年份總是被解釋為 4 位數字。例如（對於 20000 年）：to_date('200001131', 'YYYYMMDD')將會被解釋成一個 4 位數字的年份，而不是在年份後使用一個非數字分隔符，像to_date('20000-1131', 'YYYY-MMDD')或to_date('20000Nov31', 'YYYYMonDD')。
  • 在從字符串向timestamp或date的轉換中， 如果有YYY、YYYY或者Y,YYY域， 那麼CC（世紀）域會被忽略。如果CC和YY或Y一起使用， 那麼年份被計算時會認為年份位於指定的世紀中。如果該世紀被指定但是年份沒有被指定，將假定用該世紀的第一年。
  • 一個 ISO 8601 周編號的日期（與一個格裏高利日期相區別）可以用兩種方法之一被指定為to_timestamp和to_date：
    • 年、周編號和工作日：例如to_date('2006-42-4', 'IYYY-IW-ID')返回日期2006-10-19。如果你忽略工作日，它被假定為 1（周一）。
    • 年和一年中的日：例如to_date('2006-291', 'IYYY-IDDD')也返回2006-10-19。

嚐試使用一個混合了 ISO 8601 周編號和格裏高利日期的域來輸入一個日期是無意義的，並且將導致一個錯誤。在一個 ISO 周編號的年的環境下，一個"月"或"月中的日"的概念沒有意義。在一個格裏高利年的環境下，ISO 周沒有意義。用戶應當避免混合格裏高利和 ISO 日期聲明。

Caution

雖然to_date將會拒絕混合使用格裏高利和 ISO 周編號日期的域， to_char卻不會，因為YYYY-MM-DD (IYYY-IDDD) 這種輸出格式也會有用。但是避免寫類似IYYY-MM-DD的東西，那會得到在 起始年附近令人驚訝的結果。

  • 在從字符串到timestamp的轉換中，毫秒（MS）和微秒（US）值都被用作小數點後的秒位。例如to_timestamp('12:3', 'SS:MS')不是 3 毫秒, 而是 300，因為該轉換把它看做 12 + 0.3 秒。這意味著對於格式SS:MS而言，輸入值12:3、12:30和12:300指定了相同數目的毫秒。要得到三毫秒，你必須使用 12:003，轉換會把它看做 12 + 0.003 = 12.003 秒。

下麵是一個更複雜的例子∶to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US')是 15 小時、12 分鍾和 2 秒 + 20 毫秒 + 1230微秒 = 2.021230 秒。

  • to_char(..., 'ID')的一周中日的編號匹配extract(isodow from ...)函數，但是to_char(..., 'D')不匹配extract(dow from ...)的日編號。
  • to_char(interval)格式化HH和HH12為顯示在一個 12 小時的時鍾上，即零小時和 36 小時輸出為12，而HH24會輸出完整的小時值，對於間隔它可以超過 23.

Table 9-26展示了可以用於格式化數字值的模版模式。

Table 9-26. 用於數字格式化的模板模式


image


數字格式化的用法須知：

  • 使用SG、PL或MI格式化的符號並不掛在數字上麵； 例如，to_char(-12, 'MI9999')生成'- 12'，而to_char(-12, 'S9999')生成 ' -12'。Oracle 裏的實現不允許在9前麵使用MI，而是要求9在MI前麵。
  • 9導致一個值，這個值的位數好像有那麼多個9在那裏。如果一個數字不可用，它將輸出一個空格。
  • TH不會轉換小於零的數值，也不會轉換小數。
  • PL、SG和TH是PostgreSQL擴展。
  • 帶有to_char的V會把輸入值乘上10^n，其中n是跟在V後麵的位數。帶有to_number的V以類似的方式做除法。to_char和to_number不支持使用結合小數點的V（例如，不允許99.9V99）。
  • EEEE（科學記數法）不能和任何其他格式化模式或修飾語（數字和小數點模式除外）組合在一起使用，並且必須位於格式化字符串的最後（例如9.99EEEE是一個合法的模式）。
  • 某些修飾語可以被應用到任何模板來改變其行為。例如，FM9999是帶有FM修飾語的9999模式。Table 9-27中展示了用於數字格式化模式修飾語。

Table 9-27. 用於數字格式化的模板模式修飾語


image


Table 9-28展示了一些使用to_char函數的例子。

Table 9-28. to_char例子


114

Table 9-30展示了可用於處理日期/時間值的函數，其細節在隨後的小節中描述。Table 9-29演示了基本算術操作符 （+、*等）的行為。 而與格式化相關的函數。

所有下文描述的接受time或timestamp輸入的函數和操作符實際上都有兩種變體： 一種接收time with time zone或timestamp with time zone， 另外一種接受time without time zone或者 timestamp without time zone。為了簡化，這些變種沒有被獨立地展示。此外，+和*操作符都是可交換的操作符對（例如，date + integer 和 integer + date）；我們隻顯示其中一個。

Table 9-29. 日期/時間操作符


image


Table 9-30. 日期/時間函數


115


除了這些函數以外，還支持 SQL 操作符OVERLAPS：


(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

這個表達式在兩個時間域（用它們的端點定義）重疊的時候得到真，當它們不重

最後更新：2017-08-17 19:33:03

  上一篇：go  JAVASCRIPT作為功能編程介紹
  下一篇：go  大大神告訴你如何衡量APP開發價格