pg正则表达式函数（pg数据库正则表达式函数）

## PostgreSQL 正则表达式函数

简介

PostgreSQL 提供了强大的正则表达式支持，允许用户使用正则表达式进行模式匹配和字符串操作。这些函数基于 POSIX 标准的正则表达式语法，并提供了一些扩展功能。利用这些函数，可以高效地进行数据验证、搜索、替换和提取等操作。### 1. 基本匹配函数

`regexp_match(string, pattern [, flags])`

: 尝试将 `pattern` 匹配到 `string`。如果匹配成功，返回一个包含匹配捕获组的文本数组；否则返回 `NULL`。`flags` 参数可选，用于指定匹配模式，例如 `'i'` 表示大小写不敏感，`'g'` 表示全局匹配（PostgreSQL 15 及更高版本）。```sqlSELECT regexp_match('foobarbaz', 'b(..)'); -- 返回 {'bar'}SELECT regexp_match('foobarbaz', 'b(..)', 'g'); -- PostgreSQL 15+，返回 {'bar'}```

`substring(string from pattern)`

: 从 `string` 中提取与 `pattern` 匹配的子字符串。如果未找到匹配项，则返回 `NULL`。```sqlSELECT substring('foobarbaz' from 'b.

'); -- 返回 'barbaz'```

`regexp_replace(string, pattern, replacement [, flags])`

: 将 `string` 中与 `pattern` 匹配的部分替换为 `replacement`。`flags` 参数与 `regexp_match` 类似。```sqlSELECT regexp_replace('foobarbaz', 'b..', 'xxx'); -- 返回 'fooxxxbaz'SELECT regexp_replace('foobarbaz', 'b..', 'xxx', 'g'); -- 返回 'fooxxxaz' 注意只替换了一次，因为 PostgreSQL < 15 'g' 的作用与预期不同```### 2. POSIX 正则表达式函数PostgreSQL 还提供了一组以 `~`、`~

`、`!~`、`!~

` 为操作符的 POSIX 正则表达式匹配函数。

`string ~ pattern`：大小写敏感匹配。

`string ~

pattern`：大小写不敏感匹配。

`string !~ pattern`：大小写敏感不匹配。

`string !~

pattern`：大小写不敏感不匹配。```sql SELECT 'foobar' ~ 'bar'; -- 返回 true SELECT 'foobar' ~

'Bar'; -- 返回 true SELECT 'foobar' !~ 'baz'; -- 返回 true ```### 3. 其他相关函数

`regexp_split_to_array(string, pattern [, flags])`

: 将 `string` 根据 `pattern` 分割成一个文本数组。`flags` 参数与 `regexp_match` 类似。```sqlSELECT regexp_split_to_array('foo,bar,baz', ','); -- 返回 {'foo','bar','baz'}```

`regexp_split_to_table(string, pattern [, flags])`

: 将 `string` 根据 `pattern` 分割成一个表（集合）。`flags` 参数与 `regexp_match` 类似。```sqlSELECT

FROM regexp_split_to_table('foo,bar,baz', ',');```### 4. Flags 参数详解`flags` 参数是一个字符串，可以包含以下标志：

`'i'`：大小写不敏感匹配。

`'g'`：全局匹配 (PostgreSQL 15 及更高版本. 在早期版本中，其行为与预期不同，并可能导致混淆).

`'c'`：区分大小写匹配 (默认)。

`'s'`：点`.`匹配任何字符，包括换行符。

`'m'`：多行模式。`^` 和 `$` 匹配每行的开头和结尾。

`'n'`：换行符敏感。### 5. 使用建议

理解不同函数之间的区别，选择合适的函数进行操作。

注意 `flags` 参数的影响，特别是 `'g'` 在不同 PostgreSQL 版本中的差异。

对于复杂的正则表达式，可以使用在线工具进行测试和调试。通过熟练掌握 PostgreSQL 的正则表达式函数，可以 significantly 提升数据处理的效率和灵活性。 这对于数据清洗、验证、搜索和提取等任务至关重要。

PostgreSQL 正则表达式函数**简介**PostgreSQL 提供了强大的正则表达式支持，允许用户使用正则表达式进行模式匹配和字符串操作。这些函数基于 POSIX 标准的正则表达式语法，并提供了一些扩展功能。利用这些函数，可以高效地进行数据验证、搜索、替换和提取等操作。

1. 基本匹配函数* **`regexp_match(string, pattern [, flags])`**: 尝试将 `pattern` 匹配到 `string`。如果匹配成功，返回一个包含匹配捕获组的文本数组；否则返回 `NULL`。`flags` 参数可选，用于指定匹配模式，例如 `'i'` 表示大小写不敏感，`'g'` 表示全局匹配（PostgreSQL 15 及更高版本）。```sqlSELECT regexp_match('foobarbaz', 'b(..)'); -- 返回 {'bar'}SELECT regexp_match('foobarbaz', 'b(..)', 'g'); -- PostgreSQL 15+，返回 {'bar'}```* **`substring(string from pattern)`**: 从 `string` 中提取与 `pattern` 匹配的子字符串。如果未找到匹配项，则返回 `NULL`。```sqlSELECT substring('foobarbaz' from 'b.*'); -- 返回 'barbaz'```* **`regexp_replace(string, pattern, replacement [, flags])`**: 将 `string` 中与 `pattern` 匹配的部分替换为 `replacement`。`flags` 参数与 `regexp_match` 类似。```sqlSELECT regexp_replace('foobarbaz', 'b..', 'xxx'); -- 返回 'fooxxxbaz'SELECT regexp_replace('foobarbaz', 'b..', 'xxx', 'g'); -- 返回 'fooxxxaz' 注意只替换了一次，因为 PostgreSQL < 15 'g' 的作用与预期不同```

2. POSIX 正则表达式函数PostgreSQL 还提供了一组以 `~`、`~*`、`!~`、`!~*` 为操作符的 POSIX 正则表达式匹配函数。* `string ~ pattern`：大小写敏感匹配。 * `string ~* pattern`：大小写不敏感匹配。 * `string !~ pattern`：大小写敏感不匹配。 * `string !~* pattern`：大小写不敏感不匹配。```sql SELECT 'foobar' ~ 'bar'; -- 返回 true SELECT 'foobar' ~* 'Bar'; -- 返回 true SELECT 'foobar' !~ 'baz'; -- 返回 true ```

3. 其他相关函数* **`regexp_split_to_array(string, pattern [, flags])`**: 将 `string` 根据 `pattern` 分割成一个文本数组。`flags` 参数与 `regexp_match` 类似。```sqlSELECT regexp_split_to_array('foo,bar,baz', ','); -- 返回 {'foo','bar','baz'}```* **`regexp_split_to_table(string, pattern [, flags])`**: 将 `string` 根据 `pattern` 分割成一个表（集合）。`flags` 参数与 `regexp_match` 类似。```sqlSELECT * FROM regexp_split_to_table('foo,bar,baz', ',');```

4. Flags 参数详解`flags` 参数是一个字符串，可以包含以下标志：* `'i'`：大小写不敏感匹配。 * `'g'`：全局匹配 (PostgreSQL 15 及更高版本. 在早期版本中，其行为与预期不同，并可能导致混淆). * `'c'`：区分大小写匹配 (默认)。 * `'s'`：点`.`匹配任何字符，包括换行符。 * `'m'`：多行模式。`^` 和 `$` 匹配每行的开头和结尾。 * `'n'`：换行符敏感。

5. 使用建议* 理解不同函数之间的区别，选择合适的函数进行操作。 * 注意 `flags` 参数的影响，特别是 `'g'` 在不同 PostgreSQL 版本中的差异。 * 对于复杂的正则表达式，可以使用在线工具进行测试和调试。通过熟练掌握 PostgreSQL 的正则表达式函数，可以 significantly 提升数据处理的效率和灵活性。 这对于数据清洗、验证、搜索和提取等任务至关重要。