9.4. 字符串函数和操作符

本节描述了用于检查和操作字符串数值的函数和操作符。 在这个环境中的字符串包括character,character varying, text类型的值。除非另外说明，所有下面列出的函数都可以处理这些类型， 不过要小心的是，在使用character类型的时候，需要注意自动填充的潜在影响。 有些函数还可以处理位串类型。

SQL定义了一些字符串函数，用特定的关键字而不是逗号来分隔参数。 详情请见Table 9-5。PostgreSQL 也提供了使用正常的函数调用语法实现的这些函数的版本(参阅 Table 9-6)。

Note: 在PostgreSQL 8.3之前， 这些函数将默默接受一些非字符串数据类型的值，由于存在从这些数据类型到text 的隐式强制转换，转换后的它们经常发生意外的行为，因此删除了隐式强制转换。 然而，字符串连接操作符(||)仍接受非字符串输入， 只要至少有一个输入是字符串类型，如Table 9-5所示。 对于其它情况下，如果你需要重复以前的行为，插入一个明确的强制转换到text。

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

还有额外的字符串操作函数可以用，它们在Table 9-6列出。 它们有些在内部用于实现Table 9-5 列出的SQL标准字符串函数。

Table 9-6. 其它字符串函数

world

(2 rows) | | repeat(```string` `text`, `number` `int`) | `text` | 将`string`重复`number`次 | `repeat('Pg', 4)` | `PgPgPgPg` | |replace(string` `text`, `from` `text`, `to` `text`) | `text` | 把字符串`string`里出现地所有子字符串`from` 替换成子字符串`to` | `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`(缺省时为空白)， 把`string`填充到`length`长度。 如果`string`已经比`length`长则将其从尾部截断。 | `rpad('hi', 5, 'xy')` | `hixyx` | |rtrim(string` `text` [, `characters` `text`]) | `text` | 从字符串`string`的结尾删除只包含 `characters`中字符(缺省是个空白)的最长的字符串。 | `rtrim('trimxxxx', 'x')` | `trim` | | ``split_part(string`text, delimiter text, field int) | text | 根据delimiter分隔string 返回生成的第 field 个子字符串(1为基)。 | split_part('abc~@~def~@~ghi', '~@~', 2) | def | | strpos(```string`, `substring`) | `int` | 指定的子字符串的位置。和`position(substringinstring)一样，不过参数顺序相反。 |strpos('high', 'ig')|2| | ``substr(```string, from [, count]) | text | 抽取子字符串。和substring(``string from from for count))一样 | substr('alphabet', 3, 2) | ph | | to_ascii(```string` `text` [, `encoding` `text`]) | `text` | 把`string`从其它编码转换为ASCII (仅支持`LATIN1`, `LATIN2`, `LATIN9`, `WIN1250`编码)。 | `to_ascii('Karel')` | `Karel` | |to_hex(number` `int` or `bigint`) | `text` | 把`number`转换成十六进制表现形式 | `to_hex(2147483647)` | `7fffffff` | | ``translate(string`text, from text, to text) | text | 把在string中包含的任何匹配from 中字符的字符转化为对应的在to中的字符。 如果from比to长， 删掉在from中出现的额外的字符。 | translate('12345', '143', 'ax') | a2x5 |

concat, concat_ws和format 函数是可变的，所以用VARIADIC 关键字标记传递的数值以连接或者格式化为一个数组是可能的。 见Section 35.4.5。数组的元素对函数来说是单独的普通参数。 如果可变数组的元素是NULL，那么concat和concat_ws 返回NULL，但是format把NULL作为零元素数组对待。

又见Section 9.20里面的聚集函数string_agg。

Table 9-7. 内置的转换

9.4.1. 格式化

函数format生成根据格式字符串格式化了的输出，风格类似于C函数sprintf。

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

_formatstr_是指定结果如何格式化的格式字符串。格式字符串中的文本直接拷贝到结果中， 除非已经使用了格式说明符。格式说明符在字符串中作为占位符使用， 定义后续函数参数应该格式化并且插入到结果中。每个_formatarg_ 参数根据这种数据类型的通常输出规则转化为文本，然后根据格式说明符格式化并且插入到结果中。

格式说明符由%字符引进，格式为

%[_position_][_flags_][_width_]_type_

组件的字段有：

_position_ (optional)

_n_$格式的字符串，这里的_n_ 是要打印的参数的索引。索引为1表示在_formatstr_ 之后的第一个参数。如果省略了_formatstr_， 默认使用序列中的下一个参数。

_flags_ (optional)

附加选项，控制如何格式化格式说明符的输出。当前只支持负号(-)， 负号导致格式说明符的输出是左对齐的。这是没有影响的，除非指定了 _width_字段。

_width_ (optional)

声明字符数的minimum值用来显示格式说明符的输出。需要补充宽度时， 空白添加到输出的左侧或右侧（取决于-标志）。一个比较小的宽度不会导致输出的截断， 只是简单的忽略了。宽度可以用下列方法指定：一个正整数；一个星号(*) 表示使用下一个函数参数作为宽度；或一个格式为*``_n_$ 的字符串表示使用第_n_个函数参数作为宽度。

如果宽度来自函数参数，那么这个参数在作为格式说明符的数值之前消耗掉。 如果宽度参数是负的，那么结果是左对齐的（就像声明了-标志一样）， 并且字段长度为abs(_width_)。

_type_ (required)

格式转换的类型用来产生格式说明符的输出。支持下列的类型：

• s格式参数值为简单的字符串。空值作为空字符串对待。

• I将参数值作为SQL标识符对待，如果需要，双写它。值为空是错误的。

• L引用参数值作为SQL文字。空值用字符串NULL显示，没有引用。

除了上述的格式说明符，特殊的序列%%可以用作输出%字符。

这里有一些基本的格式转换的例子：

SELECT format('Hello %s', 'World');
_Result:_ <samp class="literal">Hello World</samp>

SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
_Result:_ <samp class="literal">Testing one, two, three, %</samp>

SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
_Result:_ <samp class="literal">INSERT INTO "Foo bar" VALUES('O''Reilly')</samp>

SELECT format('INSERT INTO %I VALUES(%L)', 'locations', E'C:\\Program Files');
_Result:_ <samp class="literal">INSERT INTO locations VALUES(E'C:\\Program Files')</samp>

这里是使用_width_字段和-标志的例子：

SELECT format('|%10s|', 'foo');
_Result:_ <samp class="literal">|       foo|</samp>

SELECT format('|%-10s|', 'foo');
_Result:_ <samp class="literal">|foo       |</samp>

SELECT format('|%*s|', 10, 'foo');
_Result:_ <samp class="literal">|       foo|</samp>

SELECT format('|%*s|', -10, 'foo');
_Result:_ <samp class="literal">|foo       |</samp>

SELECT format('|%-*s|', 10, 'foo');
_Result:_ <samp class="literal">|foo       |</samp>

SELECT format('|%-*s|', -10, 'foo');
_Result:_ <samp class="literal">|foo       |</samp>

下面是使用_position_字段的例子：

SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
_Result:_ <samp class="literal">Testing three, two, one</samp>

SELECT format('|%*2$s|', 'foo', 10, 'bar');
_Result:_ <samp class="literal">|       bar|</samp>

SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
_Result:_ <samp class="literal">|       foo|</samp>

和C函数sprintf不同，PostgreSQL的format 函数允许带有或不带有_position_字段的格式说明符在相同的格式字符串中混合使用。 没有_position_字段的格式说明符总是使用最后消耗参数的下一个参数。 另外，format函数不要求在格式字符串中使用所有函数参数。例如：

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
_Result:_ <samp class="literal">Testing three, two, three</samp>

%I和%L格式说明符对于安全构造动态SQL语句尤其有用。 参阅Example 40-1。