学习如何在PostgreSQL中使用Regex表达式

图片来自 Pexels

在本文中,我将讨论 PostgreSQL 中的模式匹配和正则表达式。 PostgreSQL 是一个开源的关系数据库管理系统,在最近几天获得了很大的普及。 PostgreSQL 可以快速且轻松地安装在本地或流行的云平台(如 Azure、AWS 等)上。在使用任何选择的数据库时要掌握的一项重要技能是学习和编写 SQL 查询。 PostgreSQL 还支持原生 ANSI SQL 查询,初学者很容易开始为 PostgreSQL 数据库编写查询。

编写查询的一个重要方面是用户应该能够过滤和选择系统需要访问的数据。额外的或不必要的数据会增加带宽并降低查询的性能。因此,SQL 提供了一个过滤谓词“WHERE”,用户可以使用它过滤查询并选择仅匹配过滤谓词的结果。例如,如果我们想选择在计算机科学部门工作的用户,那么我们将编写如下 SQL 查询。

SELECT * FROM users WHERE department u003d ‘Computer Science’

请注意,过滤谓词使用等号 (u003d) 和用户表中部门列中要匹配的确切短语。但是,在某些情况下,可能需要根据确切短语的一部分进行过滤。换句话说,用户可能需要过滤所有匹配短语的一部分但不完全匹配的记录。例如,让我们考虑有两个系,计算机科学和计算数学。现在,过滤这两个部门的查询可以编写如下。

SELECT * FROM users WHERE department LIKE ‘Comput%’

请注意,查询中如何使用 LIKE 运算符仅过滤部分精确短语,后跟“%”符号。 ‘%’ 操作符是一个通配符操作符,表示可以在匹配的短语之后选择任何内容。

PostgreSQL 中的正则表达式

到目前为止,我们已经学习了如何使用 WHERE 子句过滤查询并使用 LIKE 运算符匹配模式。在正常的 SQL 操作的情况下,LIKE 运算符很好,但是在过滤大型数据库时,LIKE 运算符似乎存在一些性能问题。此外,使用 LIKE 运算符的过滤条件仅限于通过包含通配符 (%) 来查找模式。为了克服这个问题,PostgreSQL 提供了一种使用正则表达式进行模式匹配的高级方法。长期以来,正则表达式在编程语言中被大量使用,但是,在 SQL 语句中使用这些正则表达式可以使查询高度动态化,并且在大型数据库中表现更好。 PostgreSQL 中的正则表达式使用 TILDE (~) 运算符实现,并使用“.*”作为通配符运算符。

图 1 — 在 PostgreSQL 数据库中使用正则表达式

如上图所示,我们在 PostgreSQL 中使用正则表达式,使用 TILDE (~) 运算符和通配符“.*”。此查询将从 GreaterManchesterCrime 表中选择具有有效 CrimeID 的所有记录。由于模式条件只是通配符,它将从表中获取所有记录。另外,在 PostgreSQL 中编写正则表达式时需要注意的另一点是模式匹配语句总是以“^”运算符开头并以“$”符号结尾。这两个运算符标记正则表达式语句的开始和结束。总结图,在使用通配符过滤所有记录的同时,表达式可以实现为“^.*$”。

正则表达式 — 以数字或字符开头的字符串

在上一节中,我们学习了如何仅使用通配符运算符来实现正则表达式。现在,我们将向前迈出一步,尝试获取以字符或数字开头的记录,用于列 CrimeID。

在 PostgreSQL 中,字母数字字符可以通过使用模式“[a-z]”或“[A-Z]”来匹配,具体取决于我们尝试匹配的情况。这里要注意的重要一点是,由于 PostgreSQL 区分大小写,因此,必须指定我们试图在模式中匹配的确切大小写。同样,对于匹配的数字,我们可以使用“[0–9]”或“\d”来笑脸。现在我们对使用 Regex 过滤字符和数字有了一些想法,让我们继续在数据库上实现它。

用于匹配字符 –

选择 * 从 GreaterManchesterCrime WHERE CrimeID ~ '^[A-Z].*$'

图 2 — 在 PostgreSQL 中使用正则表达式 — 大写模式匹配

如上图所示,CrimeID 字段已被过滤,仅包含 ID 以大写字母开头的记录。注意通配符“.*”在这里再次用于表示 SQL 语句中第一个字符之后的任何内容。

要过滤语句中的数字,我们可以简单地编写如下查询。

选择 * 从 GreaterManchesterCrime WHERE CrimeID ~ '^[0–9].*$'

图 3 — 在 PostgreSQL 中使用正则表达式 — 数字模式匹配

从上图中我们可以看到,只有以数字开头的CrimeID的记录被过滤以匹配正则表达式条件。

正则表达式 — 以重复数字或字符开头的字符串

在本文的前一节中,我们已经了解了如何在 PostgreSQL 中使用正则表达式语法编写 SQL 查询,以及如何在被过滤的字符串的第一个位置过滤字符和数字。现在,让我们为要求添加一些更复杂的内容,并过滤开头以多个字符或数字开头的记录。 PostgreSQL 只需在花括号内提供计数,就可以非常容易地指定重复次数。或者,您也可以多次重复相同的字符模式匹配以匹配您的条件。例如,我们想过滤所有以两个字符开头的记录。这个查询可以写成如下。

选择 * 从 GreaterManchesterCrime WHERE CrimeID ~ '^[A-Z] [A-Z].*$'

或者

选择 * 从 GreaterManchesterCrime WHERE CrimeID ~ '^[A-Z] {2}.*$'

图 4 — 在 PostgreSQL 中使用正则表达式 — 重复大写模式匹配

如上图所示,我们使用花括号中的数字来表示在列中查找模式的次数。这使得查询非常动态,因为您可以指定要在查询中搜索的任意数量的字符,您将获得结果。同样,您也可以为重复数值实现相同的逻辑。在这种情况下,查询可以编写如下。

选择 * 从 GreaterManchesterCrime WHERE CrimeID ~ '^[0–9]{2}.*$'

图 5 — 在 PostgreSQL 中使用正则表达式 — 重复数字模式匹配

上图展示了在过滤重复数值的同时使用正则表达式进行模式匹配的实现。

正则表达式 — 以字符和数字开头的字符串

在本文的最后一部分中,我们将结合到目前为止所看到的内容。我们将构建查询并匹配将过滤以字符开头并后跟数字的记录的模式。要形成本规范的查询,您可以在正则表达式中组合字母和数字字符的条件。 SQL 语句可以写成如下。

选择 * 从 GreaterManchesterCrime WHERE CrimeID ~ '^[A-Z][0–9].*$'

图 6 — 在 PostgreSQL 中使用正则表达式 — 字母数字模式匹配

结论

在本文中,我们深入探讨了在 PostgreSQL 中使用正则表达式编写 SQL 语句。正则表达式帮助我们编写动态 SQL 语句,可用于匹配数据库中列的模式。当您不需要查询中的精确匹配时,模式匹配很有用,但是,您希望查看与条件匹配的所有记录。 PostgreSQL 中的模式匹配也可以使用 SQL LIKE 运算符来实现,但是搜索是有限的。正则表达式提供了更大的灵活性,并允许动态控制要匹配的模式范围。要了解有关在 PostgreSQL 中使用正则表达式进行模式匹配的更多信息,我建议您遵循官方教程。