在 sql 中使用正则表达式

新建测试表：

-- Table: public.table01

-- DROP TABLE public.table01;

CREATE TABLE IF NOT EXISTS public.table01
(
    id bigint NOT NULL,
    name character varying COLLATE pg_catalog."default" NOT NULL,
    age integer,
    CONSTRAINT table01_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.table01
    OWNER to postgres;

语法

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

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

查询特定字符或字符串开头的记录 ^

SELECT * FROM tableName WHERE fieldName~'^a';

• 查询表 tableName 中 fieldName 中以 小写 'a' 开头的数据

测试 ^

SELECT * FROM public.table01 where name~'^张';

返回结果

查询特定字符或字符串结尾的记录

SELECT * FROM tableName WHERE fieldName ~ 'a';

• 查询表 tableName 中 fieldName 中以 小写 'a' 结尾的数据

测试

SELECT * FROM public.table01 where name~'三';

返回结果

用符号 "." 来代替字符串中任意一个字符

测试 .

SELECT * FROM public.table01 where name~'张.三';

返回结果

使用 "*" 和 "+" 匹配多个字符

"*" 匹配前面的字符任意多次，包括0次

"+" 匹配前面的字符至少1次

测试 *

SELECT * FROM public.table01 where name ~ '肖七*';  --'七'可以一个都没有

返回结果

测试 +

SELECT * FROM public.table01 where name ~ '肖七+';   -- '七'至少一个

返回结果

匹配指定字符串（单个、多个(A|B)）

测试

SELECT * FROM public.table01 where name ~ '肖|三';

• 查询name中有 '肖' 或者 '三' 的数据

返回结果

匹配指定字符串中的任意一个（[]）

测试 [ ]

SELECT * FROM public.table01 where name ~ '[肖七]';

• 查询字段 name中含有 '肖' 或者 '七' 或者 '肖七' 的数据

返回结果

和 '|' 比较下

SELECT * FROM public.table01 where name ~ '肖|肖七';

相当于 '肖|七|肖七'

匹配指定字符串以外的字符

测试

SELECT * FROM public.table01 where name !~ '肖|三';

• 查询字段 name中不含 '肖' 或者 '三' 的数据

返回结果

和 "SELECT * FROM public.table01 where name ~ '肖|三'" 正好相反

使用 {M,} 或 {M,N} 指定字符串连续出现的次数

"{M,}" 匹配前面的字符至少M次

"{M,N}" 匹配前面的字符至少M次，至多N次

测试 {M,}

SELECT * FROM public.table01 where name ~ '八{2,}';

• 查询字段 name中含有至少2个 '八' 的数据

返回结果

测试 {M,N}

SELECT * FROM public.table01 where name ~ '八{0,1}八';

• 查询字段 name中含有 '八' 或者 '八八' 的数据

返回结果

和 "SELECT * FROM public.table01 where name ~ '八{1,2}八'" 比较下