SQL中的字符串正则表达式常见示例代码

因数据库系统而异。以下是几种常见数据库的正则表达式使用示例：

1. MySQL（8.0+ 支持正则表达式）

-- 基本匹配
SELECT * FROM users WHERE name REGEXP '^J.*n$';  -- 以J开头，n结尾

-- 检查邮箱格式
SELECT email FROM users WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$';

-- 提取数字
SELECT REGEXP_SUBSTR(description, '[0-9]+') FROM products;

-- 替换
SELECT REGEXP_REPLACE(phone, '\\D', '') FROM contacts;  -- 移除非数字字符

-- 验证复杂密码
SELECT * FROM accounts 
WHERE password REGEXP '^(?=.*[A-Z])(?=.*[a-z])(?=.*[0-9]).{8,}$';

2. PostgreSQL

-- 使用 ~ 进行匹配
SELECT * FROM users WHERE name ~ '^J.*n$';

-- 不区分大小写匹配
SELECT * FROM users WHERE name ~* '^john';

-- 不匹配
SELECT * FROM users WHERE name !~ '^test';

-- 提取匹配部分
SELECT regexp_matches(description, '\\d+', 'g') FROM products;

-- 替换
SELECT regexp_replace(phone, '[^0-9]', '', 'g') FROM contacts;

-- 分割字符串
SELECT regexp_split_to_table('a,b,c', ',');

3. Oracle

-- REGEXP_LIKE 匹配
SELECT * FROM employees WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,}$', 'i');

-- 提取子串
SELECT REGEXP_SUBSTR(description, '[0-9]+') FROM products;

-- 替换
SELECT REGEXP_REPLACE(phone, '[^0-9]', '') FROM contacts;

-- 计算匹配次数
SELECT REGEXP_COUNT(text, 'error', 1, 'i') FROM logs;

4. SQL Server

-- SQL Server 2016+ 支持
SELECT * FROM users WHERE name LIKE '%[0-9]%';  -- 基础模式匹配

-- 更复杂的需要使用 PATINDEX
SELECT * FROM products 
WHERE PATINDEX('%[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%', phone) > 0;

-- SQL Server 2017+ 支持更完整的正则表达式（通过 OLE 自动化）

5. SQLite

-- 需要安装正则表达式扩展
-- 或使用 LIKE/GLOB（有限支持）
SELECT * FROM users WHERE name LIKE 'J%n';

-- 使用 GLOB（Unix风格通配符）
SELECT * FROM users WHERE name GLOB 'J*n';

🔍 常用正则表达式模式

数据验证

-- 邮箱验证
'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'

-- 手机号（简单中国）
'^1[3-9]\\d{9}$'

-- 身份证号（18位）
'^[1-9]\\d{5}(18|19|20)\\d{2}((0[1-9])|(1[0-2]))(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$'

-- URL
'^(https?|ftp)://[^\s/$.?#].[^\s]*$'

数据清洗

-- 提取数字
'[0-9]+'

-- 移除非数字字符
'[^0-9]'

-- 提取中文
'[\u4e00-\u9fa5]+'  -- MySQL可能需要二进制模式

-- 提取邮箱用户名
'^([^@]+)@'

实际应用示例

-- 查找包含特定单词的行
SELECT * FROM articles WHERE content REGEXP '\\b(SQL|数据库)\\b';

-- 提取价格
SELECT REGEXP_SUBSTR(description, '\\$[0-9]+(\\.[0-9]{2})?') FROM products;

-- 验证日期格式
SELECT * FROM logs 
WHERE date_string REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';