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
=# 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)
