PostgreSql 字符串匹配

news/2024/7/9 20:12:01 标签: postgresql, 数据库

一、概述

  PostgreSQL 提供了三种独立的实现模式匹配的方法：SQL LIKE 操作符、更近一些的 SIMILAR TO 操作符（SQL:1999 里添加进来的）和 POSIX 正则表达式。虽然大部分的正则表达式搜索都能被很快地执行，但是正则表达式仍可能被人为地设置成需要任意长的时间和任意量的内存进行处理，如果必须这样做，建议加上语句超时限制。使用 SIMILAR TO 模式的搜索具有同样的安全性危险，LIKE 搜索比其他两种选项简单得多，要更安全些。

二、SQL LIKE 操作符

2.1 语法

string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]

参数说明：

string：待匹配的字符串。
pattern：匹配规则。若不包含百分号或者下划线，那么只匹配串本身，这时 LIKE 的行为等价于等号操作符，下划线（_）代表匹配任何单个字符，百分号（%）匹配任何零或更多个字符。
ESCAPE：自定义转义字符，默认为反斜线（\）。

补充：
PostgreSQL 还提供了标准 SQ L中没有的 ILIKE 操作符，用于忽略大小写的模式匹配。

~~：等效于 LIKE。
~~*：等效于 ILIKE。
!~~：等效于 NOT LIKE。
!~~*：操作符 NOT ILIKE。

2.2 示例

--正常字符匹配
postgres=# select * from t;
 id |   name
----+----------
  1 | XIAOH
  2 | XIAOO
  3 | xiaoguo
  4 | xiaowan
  5 | xiaopiao
  6 | xiaopen
(6 rows)

postgres=# select * from t where name like 'xiao%';
 id |   name
----+----------
  3 | xiaoguo
  4 | xiaowan
  5 | xiaopiao
  6 | xiaopen
(4 rows)

postgres=# select * from t where name ~~ 'xiao%';
 id |   name
----+----------
  3 | xiaoguo
  4 | xiaowan
  5 | xiaopiao
  6 | xiaopen
(4 rows)

postgres=# select * from t where name not like 'xiao%';
 id | name
----+-------
  1 | XIAOH
  2 | XIAOO
(2 rows)

postgres=# select * from t where name !~~ 'xiao%';
 id | name
----+-------
  1 | XIAOH
  2 | XIAOO
(2 rows)

postgres=# select * from t where name ilike 'xiao%';
 id |   name
----+----------
  1 | XIAOH
  2 | XIAOO
  3 | xiaoguo
  4 | xiaowan
  5 | xiaopiao
  6 | xiaopen
(6 rows)

postgres=# select * from t where name ~~* 'xiao%';
 id |   name
----+----------
  1 | XIAOH
  2 | XIAOO
  3 | xiaoguo
  4 | xiaowan
  5 | xiaopiao
  6 | xiaopen
(6 rows)

postgres=# select * from t where name not ilike 'xiao%';
 id | name
----+------
(0 rows)

postgres=# select * from t where name !~~* 'xiao%';
 id | name
----+------
(0 rows)

--加转义匹配
postgres=# select * from t;
 id | name
----+------
  1 | \
  2 | \n
  3 | \\
  4 | a\a
(4 rows)

postgres=# select * from t where name like '\%';
 id | name
----+------
(0 rows)

postgres=# select * from t where name like '\\%';
 id | name
----+------
  1 | \
  2 | \n
  3 | \\
(3 rows)

postgres=# select * from t where name like '3\%' escape '3';
 id | name
----+------
  1 | \
  2 | \n
  3 | \\
(3 rows)

postgres=# select * from t where name like '#\%' escape '#';
 id | name
----+------
  1 | \
  2 | \n
  3 | \\
(3 rows)

postgres=# select * from t where name like '@\%' escape '@';
 id | name
----+------
  1 | \
  2 | \n
  3 | \\
(3 rows)

三、SIMILAR TO 操作符

3.1 语法

string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]

参数说明：

string：待匹配的字符串。
pattern：匹配规则。若不包含百分号或者下划线，那么只匹配串本身，这时 LIKE 的行为等价于等号操作符，下划线（_）代表匹配任何单个字符，百分号（%）匹配任何零或更多个字符。
ESCAPE：自定义转义字符，默认为反斜线（\）。

除了支持上述 LIKE 的语法外，SIMILAR TO 还支持 POSIX 正则表达式匹配元字符：

|：表示选择（两个候选之一）。
*：表示重复前面的项零次或更多次。
+：表示重复前面的项一次或更多次。
?：表示重复前面的项零次或一次。
{m}：表示重复前面的项刚好m次。
{m,}：表示重复前面的项m次或更多次。
{m,n}：表示重复前面的项至少m次并且不超过n次。

可以使用圆括号()把多个项组合成一个逻辑项。一个方括号表达式[…]声明一个字符类，就像 POSIX 正则表达式一样。

3.2 示例

postgres=# select * from t;
 id |           name
----+--------------------------
  1 | ab
  2 | cdcdcdcdcdcdcdcdcdcdcdcd
  3 | efghefghefghefgh
(3 rows)

postgres=# select * from t where name similar to 'c%';
 id |           name
----+--------------------------
  2 | cdcdcdcdcdcdcdcdcdcdcdcd
(1 row)

postgres=# select * from t where name similar to '(a|c)%';
 id |           name
----+--------------------------
  1 | ab
  2 | cdcdcdcdcdcdcdcdcdcdcdcd
(2 rows)

postgres=# select * from t where name similar to '(cd)*';
 id |           name
----+--------------------------
  2 | cdcdcdcdcdcdcdcdcdcdcdcd
(1 row)

postgres=# select * from t where name similar to '(cd)+';
 id |           name
----+--------------------------
  2 | cdcdcdcdcdcdcdcdcdcdcdcd
(1 row)

postgres=# select * from t where name similar to '(ab)?';
 id | name
----+------
  1 | ab
(1 row)

postgres=# select * from t where name similar to '(efghefgh){2}';
 id |       name
----+------------------
  3 | efghefghefghefgh
(1 row)

postgres=# select * from t where name similar to '(efgh){2,}';
 id |       name
----+------------------
  3 | efghefghefghefgh
(1 row)

四、POSIX 正则表达式

4.1 匹配操作符

操作符描述例子
text ~ text → boolean字符串匹配正则表达式，大小写敏感‘thomas’ ~ ‘.*thom.*’ → t
text ~* text → boolean字符串匹配正则表达式，大小写不敏感‘thomas’ ~* ‘.*Thom.*’ → t
text !~ text → boolean字符串不匹配正则表达式，大小写敏感‘thomas’ !~ ‘.*thomas.*’ → f
text !~* text → boolean字符串不匹配正则表达式，大小写不敏感‘thomas’ !~* ‘.*Thom.*’ → f

4.2 常用匹配规则

匹配规则符描述
|选择（两个候选之一）
*重复前面的项零次或更多次
+重复前面的项一次或更多次
()括号中内容一个逻辑项
?前面的项零次或一次
*?*的非贪婪模式
+?+的非贪婪模式
???的非贪婪模式
{m}重复前面的项刚好m次
{m,}重复前面的项m次或更多次
{m,n}重复前面的项至少m次并且不超过n次
{m}?{m}的非贪婪模式
{m,}?{m,}的非贪婪模式
{m,n}{m,n}的非贪婪模式
^匹配字串的开头
$匹配字串的结尾
\n匹配换行
\r匹配回车
\t匹配制表符
\d匹配任意数字
\s匹配空格
\w匹配任意字母
\D匹配非数字
\S匹配非空格
\W匹配非字母
[a-z]匹配任意小写字母
[A-Z]匹配任意大写字母
[^a-z]匹配非小写字母
[^A-Z]匹配非大写字母
[0-9]匹配任意数字
[^0-9]匹配非数字
[abc]匹配a或b或c或ab或ac或bc
[a]+匹配一个a或多个a

4.2 常用相关函数

--substring 函数匹配字符串
postgres=# select substring('foobar',3);
 substring
-----------
 obar
(1 row)

postgres=# select substring('foobar',3,2);
 substring
-----------
 ob
(1 row)

postgres=# select substring('foobar' from '..$');
 substring
-----------
 ar

--regexp_replace 函数替换字符串
postgres=# select regexp_replace('foobar','o','x');
 regexp_replace
----------------
 fxobar
(1 row)

postgres=# select regexp_replace('foobar','o','x','g');
 regexp_replace
----------------
 fxxbar
(1 row)

postgres=# select regexp_replace('foobar','b..','x');
 regexp_replace
----------------
 foox

--regexp_match 函数匹配返回文本数组
postgres=# select regexp_match('foobar','..b');
 regexp_match
--------------
 {oob}
(1 row)

postgres=# select regexp_match('foobar','(f)(..b)');
 regexp_match
--------------
 {f,oob}
(1 row)

--regexp_matches 函数匹配返回文本数组的集合
postgres=# select regexp_matches('foobarbequebazilbarfbonk','b[^b]+','g');
 regexp_matches
----------------
 {bar}
 {beque}
 {bazil}
 {barf}
 {bonk}
(5 rows)


--regexp_split_to_table 函数把一个 POSIX 正则表达式模式当作一个定界符来分离一个串
postgres=#  select regexp_split_to_table('shi yi shi','\s');
 regexp_split_to_table
-----------------------
 shi
 yi
 shi
(3 rows)

http://www.niftyadmin.cn/n/5245561.html

相关文章

QT字体不同

QT字体不同

背景：在编写项目时，发现设置两个相同字号的font，在使用时却出现字体大小不同的情况。搜索时发现设置字体时利用的函数不同。 解决方法：Qt中定义字体大小有两个函数： （1）PointSize()&#xff1a…
阅读更多...
C语言速通笔记（全）（600粉回馈）

C语言速通笔记（全）（600粉回馈）

C语言速通课笔记 一个C语言程序有且只有一个main函数，是程序运行的起点。每个C语言程序写完后，都是先编译 .c后链接.obj最后运行..exe.c和.obj文件是无法运行的，只有.exe文件才可以运行标识符由字母，数字，下划线组成并…
阅读更多...
前端：让一个div悬浮在另一个div之上

前端：让一个div悬浮在另一个div之上

使用 CSS 的 position 属性和 z-index 属性 首先，将第二个 div 元素的 position 属性设为 relative 或 absolute。这样可以让该元素成为一个定位元素，使得后代元素可以相对于它进行定位。 然后，将要悬浮的 div 元素的 position 属性设为 ab…
阅读更多...
❀My学习Linux命令小记录（16）❀

❀My学习Linux命令小记录（16）❀

目录 ❀My学习Linux命令小记录（16）❀ 61.who指令 62.sleep指令 63.kill指令 64.top指令 65.diff指令 ❀My学习Linux命令小记录（16）❀ 61.who指令 功能说明：显示目前登录系统的用户信息。 （ps.who命…
阅读更多...
南京大学计算机学院面试准备

南京大学计算机学院面试准备

该内容是我面试南京大学计算机学院保研的时候的准备题目，最后是面试的时候问到的问题。 目录 1. 自我介绍2. 进程和线程的区别3. 循环引用4. 操作系统怎么利用多核？5. 英文介绍二叉搜索树6. 英文介绍二叉搜索树的时间复杂度7. 介绍 stackover flow8. 什…
阅读更多...
Java开发项目之KTV点歌系统设计和实现

Java开发项目之KTV点歌系统设计和实现

项目介绍 本系统实现KTV点歌管理的信息化，可以方便管理员进行更加方便快捷的管理。系统的主要使用者分为管理员和普通用户。 管理员功能模块： 个人中心、用户管理、歌曲库管理、歌曲类型管理、点歌信息管理。 普通用户功能模块： 个人中心…
阅读更多...
java正则表达式字母开头后面跟12位数字

java正则表达式字母开头后面跟12位数字

字母开头后面跟12位数字 ^[A-Za-z]\d{12}$ 验证： 验证工具地址： Java正则表达式测试
阅读更多...
简单实现Spring容器(二)

简单实现Spring容器(二)

阶段2: // 1.编写自己的Spring容器,实现扫描包,得到bean的class对象.2.扫描将 bean 信息封装到 BeanDefinition对象,并放入到Map.思路: 1.将 bean 信息封装到 BeanDefinition对象中,再将其放入到BeanDefinitionMap集合中,集合的结构大概是 key[beanName]–value[beanDefintion…
阅读更多...
最新文章

Copyright @ 2022~2023