9.4. 字串函式及運算子
本節介紹了用於檢查和操作字串的函數和運算子。在這種情況下,字串包括 character、character varying 和 text 型別的值。除非另有說明,否則下面列出的所有函數都可以在這些型別上使用,但是請注意在使用 character 型別時自動空格填充的潛在影響。其中有一些函數還支援對於位元型別的處理。
SQL 定義了一些使用關鍵字而不是逗號分隔參數的字串函數。詳情請見 Table 9.9。PostgreSQL 還提供了使用一般函數呼叫的語法,這些功能的函數版本(請參見 Table 9.10)。
在 PostgreSQL 8.3 之前的版本中,由於存在從這些資料型別到文字的隱式強制轉換,這些函數也將默默接受幾種非字串資料型別的值。這些強制轉換已被刪除,因為它們經常引起令人驚訝的結果。但是,字串連接運算子(||)仍然接受非字串輸入,只要至少一個輸入為字串型別即可,如 Table 9.9 所示。對於其他情況,如果您需要複製以前的行為,請在查詢語句中明確加入型別轉換。
Table 9.9. SQL String Functions and Operators
Function | Return Type | Description | Example | Result | ||||||
|
|
| String concatenation | `'Post' | 'greSQL'` |
| ||||
|
|
|
| String concatenation with one non-string input | `'Value: ' | 42` |
| |||
|
| Number of bits in string |
|
| ||||||
|
| Number of characters in string |
|
| ||||||
|
| Convert string to lower case |
|
| ||||||
|
| Number of bytes in string |
|
| ||||||
|
| Replace substring |
|
| ||||||
|
| Location of specified substring |
|
| ||||||
|
| Extract substring |
|
| ||||||
|
| Extract substring matching POSIX regular expression. See Section 9.7 for more information on pattern matching. |
|
| ||||||
|
| Extract substring matching SQL regular expression. See Section 9.7 for more information on pattern matching. |
|
| ||||||
`trim([leading | trailing | both] [ |
| Remove the longest string containing only characters from |
|
| ||||
`trim([leading | trailing | both] [from] |
| Non-standard syntax for |
|
| ||||
|
| Convert string to upper case |
|
|
其他字串操作的可用函數,在 Table 9.10 中列出。其中一些用於內部實作的SQL標準字符串函數,則在 Table 9.9 中列出。
Table 9.10. Other String Functions
Function | Return Type | Description | Example | Result |
|
| ASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character. For other multibyte encodings, the argument must be an ASCII character. |
|
|
|
| Remove the longest string consisting only of characters in |
|
|
|
| Character with the given code. For UTF8 the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate an ASCII character. The NULL (0) character is not allowed because text data types cannot store such bytes. |
|
|
|
| Concatenate the text representations of all the arguments. NULL arguments are ignored. |
|
|
|
| Concatenate all but the first argument with separators. The first argument is used as the separator string. NULL arguments are ignored. |
|
|
|
| Convert string to |
|
|
|
| Convert string to the database encoding. The original encoding is specified by |
|
|
|
| Convert string to |
|
|
|
| Decode binary data from textual representation in |
|
|
|
| Encode binary data into a textual representation. Supported formats are: |
|
|
|
| Format arguments according to a format string. This function is similar to the C function |
|
|
|
| Convert the first letter of each word to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters. |
|
|
|
| Return first |
|
|
|
| Number of characters in |
|
|
|
| Number of characters in |
|
|
|
| Fill up the |
|
|
|
| Remove the longest string containing only characters from |
|
|
|
| Calculates the MD5 hash of |
|
|
|
| Split |
|
|
|
| Current client encoding name |
|
|
|
| Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled. See also Example 42.1. |
|
|
|
| Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that |
|
|
|
| Coerce the given value to text and then quote it as a literal. Embedded single-quotes and backslashes are properly doubled. |
|
|
|
| Return the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is null, return |
|
|
|
| Coerce the given value to text and then quote it as a literal; or, if the argument is null, return |
|
|
|
| Return captured substring(s) resulting from the first match of a POSIX regular expression to the |
|
|
|
| Return captured substring(s) resulting from matching a POSIX regular expression to the |
|
|
|
| Replace substring(s) matching a POSIX regular expression. See Section 9.7.3 for more information. |
|
|
|
| Split |
|
|
|
| Split |
|
|
|
| Repeat |
|
|
|
| Replace all occurrences in |
|
|
|
| Return reversed string. |
|
|
|
| Return last |
|
|
|
| Fill up the |
|
|
|
| Remove the longest string containing only characters from |
|
|
|
| Split |
|
|
|
| Location of specified substring (same as |
|
|
|
| 回傳子字串(與 substring( |
|
|
|
| Returns true if |
|
|
|
| Convert |
|
|
|
| Convert |
|
|
|
| Any character in |
|
|
concat、concat_ws 和 format 函數是動態參數,因此可以將要連接或格式化的值以 VARIADIC 關鍵字標記的陣列(請參閱第 37.5.5 節)輸入。 將陣列的元素視為函數的一個普通參數。如果動態參數陣列參數為 NULL,則 concat 和 concat_ws 回傳 NULL,但是 format 將 NULL 視為零元素陣列。
另請參閱第 9.20 節中的彙總函數 string_agg。
Table 9.11. Built-in Conversions
Conversion Name | Source Encoding | Destination Encoding |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|