正则表达式与SQL数据库教程
使用正则表达式通过用例查询 Postgres 数据库:
正则表达式(又名 Regex)
- 正则表达式是一个强大的工具,广泛用于模式匹配和文本操作。
- 几乎所有编程语言都支持它们,并且经常用于文本提取、搜索和匹配文本等用例。
- 正则表达式匹配以“^”字符开头,以“$”字符结尾。
例子
- 假设我们要验证给定的字符串是否是有效的 Visa 信用卡号。
- 输入字符串为“4111111111111111”。
- 我们的正则表达式为:“4[0–9]{12}(?:[0–9]{3})?” 4[0–9]{12}:正则表达式的这一部分基本上表示该模式以 4 开头,后跟包含 (0–9) 的 12 个数字。现在总共有 13 位数字。(?:[0–9]{3}):旧的 Visa 卡有 13 位数字,因此这代表可选的 3 位数字。
现在基础知识已经清楚了,让我们讨论如何使用正则表达式进行数据库查询。
正则表达式与数据库
- 使用正则表达式可以使 SQL 查询动态化并提高性能。
- 在本文中,我们将使用支持正则表达式的 Postgres 数据库。
我们需要记住,并非所有数据库都支持正则表达式。
Postgres
- 我们可以使用 ~(TILDE) 运算符和 .*(通配符运算符)来实现 PostgreSQL 中的正则表达式。
列表
~:用于匹配正则表达式的字符串,区分大小写
# 搜索所有符合模式的电子邮件 |
2. !~:用于字符串,不匹配正则表达式,区分大小写。
# 搜索所有与模式不匹配的电子邮件 |
3. ~*:用于字符串匹配正则表达式,不区分大小写。
# s以不区分大小写的方式搜索与模式匹配的所有电子邮件 |
4. !~*:用于不匹配正则表达式的字符串,不区分大小写。
# 搜索所有与模式不匹配的邮件,不区分大小写 |
用例:通过品牌名称查找信用卡
- 我们的输入表是credit_card,其中包含卡号和到期日期。
表中记录:
postgres=# select * from credit_card; |
查询Visa信用卡
postgres=# select * from credit_card where card_number ~ '^4[0-9]{12}(?:[0-9]{3})?$';
- Visa信用卡均以4开头,旧卡为13位,新卡为16位。
- 我们的查询返回预期的 4 张以 4 开头的卡片。
美国运通:
postgres=# select * from credit_card where card_number ~ '^3[47][0-9]{13}$';
- 所有Amex信用卡均以3开头,第二位数字为4或7。总数字为15。
- 我们的查询返回预期的 3 个结果。
用例:查找所有与 emailId 模式不匹配的电子邮件
在正则表达式中,我们正在寻找characters@characters.character(例如abc@gmail.com)模式。
我们正在过滤所有不符合模式的电子邮件的查询。
select email from accounts where email !~ '^\S+@\S+\.\S+$'; |
我们的输出包含不具有上述定义模式的电子邮件。
postgres=# select email from accounts where email !~ '^\S+@\S+\.\S+$'; |