正则操作符——~、原子、量词

基本规则(4部分)

• 操作符

  • ~匹配正则，大小写相关
  • ~*匹配正则，大小写无关

• 原子

  • (re)匹配一个对re的匹配，会报告
    • (?:re)不报告
  • .任意单个字符
  • [chars]匹配任意字符

• 量词

  • * 0到n个原子
  • + 1到n个原子
  • ? 0或1个原子
  • {m} 正好匹配m个原子
  • {m,} 正好匹配m个或更多原子
  • {m,n} 正好匹配m-n个(包括m和n)原子
  • 后面加?可以变成非贪婪模式

• 约束

  • ^ 匹配开头
  • $ 匹配结尾

正则查询

• 全是数字且长度为11的记录

  select * From test_1 where name ~ '^[0-9]{11}';
  

正则替换

• 去除前后空格

  regexp_replace(xxx,E'(^\\s*)|(\\s*$)','','g')
  

• 让11，10排到9的后面

  ORDER BY 
      regexp_replace(title,'[0-9]+','g'),
      (substring(title, '([0-9]+)'))::int
  

正则分割

• 一行中 某字段 分割成 多行结果集

  regexp_split_to_table(xxx,'分隔符')
  

• 一行中 某字段 分割成 数组

  regexp_split_to_array(xxx,'分隔符')
  

• 从一连串字符中，多选其中几种，同时满足

  regexp_split_to_array(xxx,'分隔符') 
      && array['1','2']
  

  • 【实例】 xxx为乐器字符串violin,viola,cello 要多选，同时满足有violin和cello

    SELECT * 
    FROM sheet
    WHERE regexp_split_to_array(instruments,',') 
        && array['violin','cello']
    

    更加优化的WHERE语句 （先筛出有分隔符的,尽量缩小范围再split）

    WHERE instruments like '%,%' 
        AND              
        regexp_split_to_array(instruments,',') 
            && array['violin','cello']