PostgreSQL中~与^正则运算符作用及实战示例讲解

~ 运算符说明 #

• ~ 是PostgreSQL内置的区分大小写的正则匹配运算符，用于判断左侧字符串是否符合右侧正则表达式的规则，匹配成功返回布尔值true，失败返回false
• 常用衍生运算符：
  • ~*：不区分大小写的正则匹配
  • !~：区分大小写的正则不匹配
  • !~*：不区分大小写的正则不匹配
• 基础示例：

  -- 判断字符串是否包含小写字母a
  SELECT 'Apple' ~ 'a' AS res; -- 返回 false
  SELECT 'apple' ~ 'a' AS res; -- 返回 true
  SELECT 'Apple' ~* 'a' AS res; -- 返回 true
  

^ 符号说明 #

• ^ 属于正则表达式的定位元字符，代表字符串的起始位置，用于限定匹配规则必须作用在字符串的开头
• 常见搭配：
  • ^[0-9]：匹配以数字开头的字符串
  • ^[a-zA-Z]：匹配以字母开头的字符串
  • ^test：匹配以固定字符串test开头的内容
• 基础示例：

  -- 判断字符串是否以数字开头
  SELECT '123 Main St' ~ '^[0-9]' AS res; -- 返回 true
  SELECT 'Main St 123' ~ '^[0-9]' AS res; -- 返回 false
  

示例代码逻辑解析 #

你给出的SQL中left(business_address,1) ~ '^[0-9]'这段的作用是判断地址的第一个字符是否为数字，整段CASE逻辑的作用是提取地址中的街道名称：

1. 如果地址以数字开头，说明地址格式为门牌号 街道名，所以取空格分隔后的第二部分作为街道名
2. 如果地址不以数字开头，说明地址格式为街道名 其他信息，所以取空格分隔后的第一部分作为街道名

注：这段代码中的left(business_address,1)可以省略，直接写business_address ~ '^[0-9]'效果完全一致，因为^本身已经限定了匹配字符串的起始位置。

常见使用场景示例 #

-- 1. 筛选所有名字以「王」开头的用户
SELECT * FROM users WHERE username ~ '^王';

-- 2. 筛选手机号不是13、15开头的用户
SELECT * FROM users WHERE phone !~ '^(13|15)';

-- 3. 判断字符串是否为纯数字
SELECT '43210' ~ '^[0-9]+$' AS is_number; -- 返回 true
SELECT '432a10' ~ '^[0-9]+$' AS is_number; -- 返回 false

-- 4. 不区分大小写筛选标题包含postgresql的文章
SELECT * FROM articles WHERE title ~* 'postgresql';

内容的提问来源于stack exchange，提问作者João Pedro Reis Silva