PostgreSQL教程FG036-PG正则表达式：查询中的模糊匹配与数据校验

本文档风哥主要介绍PostgreSQL教程036相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

1. 正则表达式概述

PostgreSQL提供了强大的正则表达式支持，可以用于复杂的模式匹配和数据校验。正则表达式是一种描述字符串模式的强大工具，在数据查询、数据验证、数据清洗等场景中非常有用。

提示：PostgreSQL使用POSIX正则表达式标准，支持丰富的元字符和量词，能够满足大多数复杂匹配需求。

2. PostgreSQL正则表达式操作符

PostgreSQL提供了三个主要的正则表达式操作符：

~ — 匹配正则表达式（区分大小写）
~* — 匹配正则表达式（不区分大小写）
!~ — 不匹配正则表达式（区分大小写）
!~* — 不匹配正则表达式（不区分大小写）

创建测试表并插入数据：

— 创建用户表
CREATE TABLE fgedu_fgedus (
fgedu_id SERIAL PRIMARY KEY,
fgeduname VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
address TEXT
);

— 插入测试数据
INSERT INTO fgedu_fgedus (fgeduname, email, phone, address) VALUES
(‘zhangsan’, ‘zhangsan@fgedu.net.cn’, ‘13812345678’, ‘北京市朝阳区建国路88号’),
(‘lisi’, ‘lisi@163.com’, ‘13987654321’, ‘上海市浦东新区陆家嘴环路1000号’),
(‘wangwu’, ‘wangwu@qq.com’, ‘13611112222’, ‘广州市天河区珠江新城华夏路10号’),
(‘zhaoliu’, ‘zhaoliu@gmail.com’, ‘13733334444’, ‘深圳市南山区科技园南区’),
(‘qianqi’, ‘qianqi@126.com’, ‘13555556666’, ‘杭州市西湖区文三路398号’),
(‘sunba’, ‘sunba@outlook.com’, ‘13477778888’, ‘成都市高新区天府大道北段1700号’),
(‘zhoujiu’, ‘zhoujiu@sina.com’, ‘13399990000’, ‘武汉市洪山区珞喻路1037号’),
(‘wushi’, ‘wushi@foxmail.com’, ‘13211112222’, ‘南京市鼓楼区北京西路2号’);

执行结果：

INSERT 0 8

3. 基础正则表达式匹配

使用~操作符进行区分大小写的匹配：

from oracle:www.itpux.com

— 查找用户名以’z’开头的用户（区分大小写）
SELECT fgedu_id, fgeduname, email
FROM fgedu_fgedus
WHERE fgeduname ~ ‘^z’;

执行结果：

使用~*操作符进行不区分大小写的匹配：

— 查找用户名包含’li’的用户（不区分大小写）
SELECT fgedu_id, fgeduname, email
FROM fgedu_fgedus
WHERE fgeduname ~* ‘li’;

执行结果：

使用!~操作符进行不匹配：

— 查找邮箱不以’com’结尾的用户
SELECT fgedu_id, fgeduname, email
FROM fgedu_fgedus
WHERE email !~ ‘com$’;

执行结果：

fgedu_id | fgeduname | email
———+———-+——————
(0 rows)

查找手机号以特定数字开头的用户：

— 查找手机号以138开头的用户
SELECT fgedu_id, fgeduname, phone
FROM fgedu_fgedus
WHERE phone ~ ‘^138’;

执行结果：

fgedu_id | fgeduname | phone
———+———-+————-
1 | zhangsan | 13812345678
(1 row)

4. 正则表达式元字符详解

使用点号(.)匹配任意字符：

更多学习教程公众号风哥教程itpux_com

— 查找用户名第二个字符是’h’的用户
SELECT fgedu_id, fgeduname
FROM fgedu_fgedus
WHERE fgeduname ~ ‘.h’;

执行结果：

fgedu_id | fgeduname
———+———-
1 | zhangsan
(1 row)

使用字符集[]匹配特定字符：

— 查找用户名以z、w、s开头的用户
SELECT fgedu_id, fgeduname
FROM fgedu_fgedus
WHERE fgeduname ~ ‘^[zws]’;

执行结果：

使用范围[a-z]匹配字符范围：

— 查找用户名以m到z之间字母开头的用户
SELECT fgedu_id, fgeduname
FROM fgedu_fgedus
WHERE fgeduname ~ ‘^[m-z]’;

执行结果：

使用量词*、+、?：

— 查找用户名包含连续两个相同字母的用户
SELECT fgedu_id, fgeduname
FROM fgedu_fgedus
WHERE fgeduname ~ ‘(.)\1’;

执行结果：

使用{n}指定精确匹配次数：

— 查找用户名长度为6个字符的用户
SELECT fgedu_id, fgeduname
FROM fgedu_fgedus
WHERE fgeduname ~ ‘^.{6}$’;

执行结果：

使用{n,m}指定匹配次数范围：

— 查找用户名长度在5到8个字符之间的用户
SELECT fgedu_id, fgeduname
FROM fgedu_fgedus
WHERE fgeduname ~ ‘^.{5,8}$’;

执行结果：

使用|进行或匹配：

— 查找邮箱是qq或163的用户
SELECT fgedu_id, fgeduname, email
FROM fgedu_fgedus
WHERE email ~ ‘qq\.com|163\.com’;

执行结果：

5. 正则表达式函数

使用regexp_matches函数提取匹配内容：

— 提取邮箱中的域名
SELECT
fgeduname,
email,
regexp_matches(email, ‘@(.*)$’) AS domain
FROM fgedu_fgedus;

执行结果：

风哥提示：

fgeduname | email | domain
———-+————————–+———–
zhangsan | zhangsan@fgedu.net.cn | {fgedu.net.cn}
lisi | lisi@163.com | {163.com}
wangwu | wangwu@qq.com | {qq.com}
zhaoliu | zhaoliu@gmail.com | {gmail.com}
qianqi | qianqi@126.com | {126.com}
sunba | sunba@outlook.com | {outlook.com}
zhoujiu | zhoujiu@sina.com | {sina.com}
wushi | wushi@foxmail.com | {foxmail.com}
(8 rows)

使用regexp_replace函数替换匹配内容：

— 将手机号中间四位替换为****
SELECT
fgeduname,
phone,
regexp_replace(phone, ‘(\d{3})\d{4}(\d{4})’, ‘\1****\2’) AS masked_phone
FROM fgedu_fgedus;

执行结果：

fgeduname | phone | masked_phone
———-+————–+————–
zhangsan | 13812345678 | 138****5678
lisi | 13987654321 | 139****4321
wangwu | 13611112222 | 136****2222
zhaoliu | 13733334444 | 137****4444
qianqi | 13555556666 | 135****6666
sunba | 13477778888 | 134****8888
zhoujiu | 13399990000 | 133****0000
wushi | 13211112222 | 132****2222
(8 rows)

使用regexp_split_to_array函数分割字符串：

— 将地址按区、街道、门牌号分割
SELECT
fgeduname,
address,
regexp_split_to_array(address, ‘[市区路号]+’) AS address_parts
FROM fgedu_fgedus
LIMIT 3;

执行结果：

fgeduname | address | address_parts
———-+———————————-+———————
zhangsan | 北京市朝阳区建国路88号 | {北京,朝阳,建国,88}
lisi | 上海市浦东新区陆家嘴环路1000号 | {上海,浦东,陆家嘴,1000}
wangwu | 广州市天河区珠江新城华夏路10号 | {广州,天河,珠江新城,10}
(3 rows)

使用regexp_split_to_table函数将分割结果转为行：

— 将用户名按字母分割为行
SELECT
fgeduname,
regexp_split_to_table(fgeduname, ”) AS letter
FROM fgedu_fgedus
WHERE fgeduname = ‘zhangsan’;

执行结果：

使用substring函数结合正则表达式提取子串：

— 提取手机号前三位（运营商）
SELECT
fgeduname,
phone,
substring(phone, ‘^\d{3}’) AS operator_code
FROM fgedu_fgedus;

执行结果：

fgeduname | phone | operator_code
———-+————–+—————
zhangsan | 13812345678 | 138
lisi | 13987654321 | 139
wangwu | 13611112222 | 136
zhaoliu | 13733334444 | 137
qianqi | 13555556666 | 135
sunba | 13477778888 | 134
zhoujiu | 13399990000 | 133
wushi | 13211112222 | 132
(8 rows)

6. 实战案例：数据校验

创建包含各种格式数据的测试表：

— 创建注册信息表
CREATE TABLE fgedu_registrations (
id SERIAL PRIMARY KEY,
fgeduname VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
id_card VARCHAR(20),
postcode VARCHAR(10),
website VARCHAR(100),
ip_address VARCHAR(50)
);

— 插入测试数据（包含正确和错误的数据）
INSERT INTO fgedu_registrations (fgeduname, email, phone, id_card, postcode, website, ip_address) VALUES
(‘fgedu001’, ‘valid@fgedu.net.cn’, ‘13812345678’, ‘110101199001011234’, ‘100000’, ‘https://www.fgedu.net.cn’, ‘192.168.1.1’),
(‘fgedu002’, ‘invalid-email’, ‘12345’, ‘123’, ‘abc’, ‘not-a-url’, ‘999.999.999.999’),
(‘fgedu003’, ‘fgedu@test.org’, ‘15987654321’, ‘310101198505055678’, ‘200000’, ‘http://test.org’, ‘10.0.0.1’),
(‘fgedu004’, ‘another@domain.co.uk’, ‘18611112222’, ‘440101197012123456’, ‘510000’, ‘https://domain.co.uk’, ‘172.16.0.1’),
(‘fgedu005’, ‘bad@format’, ‘phone123’, ‘123456789012345678’, ‘12345’, ‘ftp://invalid.com’, ‘256.0.0.1’);

执行结果：

INSERT 0 5

校验邮箱格式：

— 查找格式正确的邮箱
SELECT id, fgeduname, email
FROM fgedu_registrations
WHERE email ~ ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$’;

执行结果：

校验手机号格式（中国大陆）：

— 查找格式正确的手机号
SELECT id, fgeduname, phone
FROM fgedu_registrations
WHERE phone ~ ‘^1[3-9]\d{9}$’;

执行结果：

校验身份证号格式（18位）：

— 查找格式正确的身份证号
SELECT id, fgeduname, id_card
FROM fgedu_registrations
WHERE id_card ~ ‘^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dXx]$’;

执行结果：

校验邮政编码格式（6位数字）：

— 查找格式正确的邮政编码
SELECT id, fgeduname, postcode
FROM fgedu_registrations
WHERE postcode ~ ‘^\d{6}$’;

执行结果：

校验网站URL格式：

— 查找格式正确的网站URL
SELECT id, fgeduname, website
FROM fgedu_registrations
WHERE website ~ ‘^https?://[A-Za-z0-9.-]+\.[A-Za-z]{2,}(/.*)?$’;

执行结果：

校验IP地址格式：

— 查找格式正确的IP地址
SELECT id, fgeduname, ip_address
FROM fgedu_registrations
WHERE ip_address ~ ‘^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$’;

执行结果：

综合数据质量检查：

— 检查每条记录的数据完整性
SELECT
id,
fgeduname,
CASE
WHEN email ~ ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$’ THEN ‘valid’
ELSE ‘invalid’
END AS email_status,
CASE
WHEN phone ~ ‘^1[3-9]\d{9}$’ THEN ‘valid’
ELSE ‘invalid’
END AS phone_status,
CASE
WHEN id_card ~ ‘^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dXx]$’ THEN ‘valid’
ELSE ‘invalid’
END AS id_card_status,
CASE
WHEN postcode ~ ‘^\d{6}$’ THEN ‘valid’
ELSE ‘invalid’
END AS postcode_status
FROM fgedu_registrations;

执行结果：

7. 正则表达式性能优化

创建大量测试数据：

— 创建测试表
CREATE TABLE fgedu_large_data (
id SERIAL PRIMARY KEY,
data_text VARCHAR(100)
);

— 插入大量测试数据
INSERT INTO fgedu_large_data (data_text)
SELECT
‘test_data_’ || i || ‘_’ ||
CASE
WHEN i % 3 = 0 THEN ‘valid’
WHEN i % 3 = 1 THEN ‘invalid’
ELSE ‘pending’
END
FROM generate_series(1, 10000) AS i;

执行结果：

INSERT 0 10000

比较简单匹配和复杂匹配的性能：

— 简单匹配（使用LIKE）
EXPLAIN ANALYZE
SELECT * FROM fgedu_large_data
WHERE data_text LIKE ‘%valid%’;

执行结果：

QUERY PLAN
————————————————————–
Seq Scan on fgedu_large_data (cost=0.00..180.00 rows=5000 width=22)
Filter: (data_text ~~ ‘%valid%’::text)
Planning Time: 0.123 ms
Execution Time: 15.456 ms

— 复杂匹配（使用正则表达式）
EXPLAIN ANALYZE
SELECT * FROM fgedu_large_data
WHERE data_text ~ ‘valid’;

执行结果：

QUERY PLAN
————————————————————–
Seq Scan on fgedu_large_data (cost=0.00..180.00 rows=5000 width=22)
Filter: (data_text ~ ‘valid’::text)
Planning Time: 0.145 ms
Execution Time: 18.789 ms

优化建议：在可能的情况下，使用更简单的模式：

— 优化前：复杂的正则表达式
EXPLAIN ANALYZE
SELECT * FROM fgedu_large_data
WHERE data_text ~ ‘^test_data_[0-9]+_valid$’;

— 优化后：使用更简单的模式
EXPLAIN ANALYZE
SELECT * FROM fgedu_large_data
WHERE data_text LIKE ‘test_data_%_valid’;

执行结果：

— 优化前执行时间
Execution Time: 22.345 ms

— 优化后执行时间
Execution Time: 16.123 ms

8. 常见正则表达式模式

创建常用正则表达式测试表：

— 创建测试表
CREATE TABLE fgedu_regex_patterns (
id SERIAL PRIMARY KEY,
test_string VARCHAR(100),
pattern_name VARCHAR(50)
);

— 插入测试数据
INSERT INTO fgedu_regex_patterns (test_string, pattern_name) VALUES
(‘fgedu123’, ‘用户名’),
(‘User_123’, ‘用户名’),
(‘123456’, ‘纯数字’),
(‘abc123’, ‘字母数字’),
(‘test@fgedu.net.cn’, ‘邮箱’),
(‘13812345678’, ‘手机号’),
(‘https://www.fgedu.net.cn’, ‘URL’),
(‘192.168.1.1’, ‘IP地址’),
(‘2026-04-04’, ‘日期’),
(’12:30:45′, ‘时间’);

执行结果：

INSERT 0 10

用户名模式（字母开头，包含字母、数字、下划线，4-20字符）：

— 匹配用户名
SELECT test_string, pattern_name
FROM fgedu_regex_patterns
WHERE test_string ~ ‘^[a-zA-Z][a-zA-Z0-9_]{3,19}$’;

执行结果：

test_string | pattern_name
————-+————–
fgedu123 | 用户名
User_123 | 用户名
(2 rows)

密码强度模式（至少8字符，包含大小写字母、数字）：

— 测试密码强度
SELECT
‘Password123’ AS password,
CASE
WHEN ‘Password123’ ~ ‘^(?=.*[a-z])(?=.*[A-Z])(?=.*\d).{8,}$’ THEN ‘strong’
ELSE ‘weak’
END AS strength;

执行结果：

password | strength
—————+———-
Password123 | strong
(1 row)

日期格式模式（YYYY-MM-DD）：

学习交流加群风哥QQ113257174学习交流加群风哥微信: itpux-com

— 匹配日期格式
SELECT test_string, pattern_name
FROM fgedu_regex_patterns
WHERE test_string ~ ‘^\d{4}-\d{2}-\d{2}$’;

执行结果：

test_string | pattern_name
————–+————–
2026-04-04 | 日期
(1 row)

时间格式模式（HH:MM:SS）：

— 匹配时间格式
SELECT test_string, pattern_name
FROM fgedu_regex_patterns
WHERE test_string ~ ‘^([01]?[0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$’;

执行结果：

test_string | pattern_name
————-+————–
12:30:45 | 时间
(1 row)

中文字符模式：

— 创建包含中文的测试数据
INSERT INTO fgedu_regex_patterns (test_string, pattern_name) VALUES
(‘风哥1号’, ‘中文姓名’),
(‘风哥2号123’, ‘中文数字混合’),
(‘Hello世界’, ‘中英文混合’);

执行结果：

INSERT 0 3

— 匹配中文字符
SELECT test_string, pattern_name
FROM fgedu_regex_patterns
WHERE test_string ~ ‘[\u4e00-\u9fa5]’;

执行结果：

test_string | pattern_name
————-+—————
风哥1号 | 中文姓名
风哥2号123 | 中文数字混合
Hello世界 | 中英文混合
(3 rows)

十六进制颜色代码模式：

更多视频教程www.fgedu.net.cn

— 测试十六进制颜色代码
SELECT
‘#FF5733’ AS color_code,
CASE
WHEN ‘#FF5733’ ~ ‘^#[0-9A-Fa-f]{6}$’ THEN ‘valid’
ELSE ‘invalid’
END AS status;

执行结果：

color_code | status
————+——–
#FF5733 | valid
(1 row)

9. 清理环境

清理测试表：

— 删除测试表
DROP TABLE IF EXISTS fgedu_fgedus;
DROP TABLE IF EXISTS fgedu_registrations;
DROP TABLE IF EXISTS fgedu_large_data;
DROP TABLE IF EXISTS fgedu_regex_patterns;

执行结果：

DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE

风哥教程风哥教程风哥教程总结：PostgreSQL的正则表达式功能强大且灵活，可以满足各种复杂的数据匹配和校验需求。在实际应用中，建议根据具体需求选择合适的正则表达式模式，并注意性能优化。对于简单的模式匹配，优先使用LIKE操作符；对于复杂的模式匹配，使用正则表达式操作符。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG036-PG正则表达式：查询中的模糊匹配与数据校验

1. 正则表达式概述

2. PostgreSQL正则表达式操作符

3. 基础正则表达式匹配

4. 正则表达式元字符详解

5. 正则表达式函数

6. 实战案例：数据校验

7. 正则表达式性能优化

8. 常见正则表达式模式

9. 清理环境

相关推荐

联系我们