PG正则表达式实例

like

drop table if exists test;
create table test (id serial, name text);
insert into test (name) values('zhangsan'),('Zhansan2'),('li_xiao_ming');
select * from test where name like 'zhang%';  --zhangsan 
select * from test where name ilike 'zhang%'; --zhangsan Zhangsan2
select * from test where name like 'li\%';    --li_xiao_ming
select * from test where name like 'li*_%' escape '*'; -- li_xiao_ming

~

select * from test where name ~ 'san';    --zhangsan Zhangsan2      匹配大小写
select * from test where name ~* 'zhang'; -- zhangsan Zhangsan2     忽略大小写
select * from test where name !~ 'san';   -- 取反 
test6=# select * from test where name ~ '^(z|Z|l)..*o+.**';   
 id |     name     
----+--------------
  1 | zhangsan
  2 | Zhansan2
  3 | li_xiao_ming
(3 rows)

test6=# 
test6=# select * from test where name ~ '^[zZl].*';   
 id |     name     
----+--------------
  1 | zhangsan
  2 | Zhansan2
  3 | li_xiao_ming
(3 rows)

select 'hellooworld' ~ '^h.*o+.*d$';

test6=# select 'hellooworld' ~ '^h.*o+.*d$';
 ?column? 
----------
 t
(1 row)

similar to

postgres=# select 'hello' similar to 'he';
 ?column? 
----------
 f
(1 row)

postgres=# select 'hello' similar to 'he%';
 ?column? 
----------
 t
(1 row)

postgres=# select 'hello' similar to 'h|o%';
 ?column? 
----------
 f
(1 row)

postgres=# select 'hello' similar to '(h|o)%';
 ?column? 
----------
 t
(1 row)

postgres=# select 'hello' similar to '(l|o)%';
 ?column? 
----------
 f
(1 row)

postgres=# select 'hello' similar to '%(l|o)%';
 ?column? 
----------
 t
(1 row)