模式匹配和正则表达式

PG中的模式匹配和正则表达式介绍

PG提供了以下三种实现模式匹配的方法

*传统SQL的like操作符

*SQL99新增的similar to操作符

*posix风格的正则表达式

还有一个模式匹配函数substring可用，可以使用similar to或posix风格的正则表达式

 

传统SQL的like操作符

百分号%代表0个或任意个字符，下划线_代表任意一个字符

若想匹配字符串中的%自身或下划线_自身，可以在字符串前加转义字符反斜杠\

转义字符也可以使用escape子句指定成其他的字符，如escape "#"指定#为转义符

转义符本身可以使用连续两个转义字符去除其特殊意义

like表达式不仅可以用在where子句中，也可以用于其他表达式中

 

PG还提供了标准SQL中没有的ilike操作符，用于忽略大小写的模式匹配

PG还提供一些与like等意思相同的操作符，如：

~~：等效于like

~~*：等效于ilike

!~~：等效于not like

!~~*：等效于not ilike

 

similar to正则表达式

similar to是SQL99标准定义的正则表达式。SQL标准的正则表达式混合了like和普通正则表达式，它是一个杂合体

similar to操作符只有在匹配整个字符串时才能成功，这一点与like相同，与普通的正则表达式只匹配部分的习惯不同。similar to与like一样，也使用下划线和百分号分别匹配单个字符和任意字符串

 

similar to还支持如下与posix正则表达式相同的模式匹配元字符：

|：表示选择两个候选项之一

*：表示重复前面的项零此或更多次

+：表示重复前面的项一次或更多次

?：表示重复前面的项零次或一次

{m}：表示重复前面的项m次

{m, }：表示重复前面的项m次或更多次

{m,n}：表示重复前面的项至少m次，不超过n次

括号()：可以作为项目分组到一个独立的逻辑项中

[...]：声明一个字符类，就像posix正则表达式

 

注意：在similar to中英文的句号“.”并不是元字符

反斜杠关闭了这些元字符所有的特殊含义，也可以用escape声明另外一个转义字符

 

posix正则表达式

posix正则表达式的模式匹配操作符：

~：匹配正则表达式，区分大小写

~*：匹配正则表达式，不区分大小写

!~：不匹配正则表达式，区分大小写

!~*：不匹配正则表达式，不分大小写

 

posix正则表达式提供了比like和similar to操作符更强大的模式匹配方法。许多unix的命令如egrep、sed、awk都使用类似的模式匹配语言

在posix正则表达式中，百分号与下划线没有像在like或similar to中的特殊意义

posix正则表达式中，只要部分匹配到字符串就返回真，与unix的grep命令一样

想匹配开头或结尾，需要使用posix中的“^”或“$”元字符

 

模式匹配函数substring

PG中有个很强大的函数substring，可以使用正则表达式

 

第一种用法：substring(<字符串>,<数字>,[数字])

后面两个参数为数字，这个函数与其他语言中的substr函数意义一样

 

第二种用法：substring(<字符串>,<字符串>)

有两个参数，都是字符串，是一种使用posix正则表达式的方式

PG中有两种正则表达式，一种称为SQL正则表达式模式，另一种被称为posix正则表达式。posix正则表达式是一般在脚本语言中使用的标准正则表达式，而SQL正则表达式首先是要遵循SQL语句中like的语法，如字符“.”在posix正则表达式中代表任意字符，而在SQL表达式中只能表示自己

SQL正则表达式中的“%”可以表示任意个字符，而在posix正则表达式需要用“.*”来表示

 

SQL正则表达式也支持以下语法：

|：表示选择两个候选项之一，在posix正则表达式中不支持

*：表示重复前面的项零次或更多次

+：表示重复前面的项一次或更多次

()：把项组合成一个逻辑项

[...]：声明一个字符类

 

similar to中使用的就是SQL正则表达式，而“~”使用的是posix正则表达式

只有两个参数的substring中的正则表达式，使用的是posix正则表达式，而不是SQL正则表达式

 

第三种用法：subtring(<字符串>,<字符串>,<字符串>)或substring(<字符串> from <字符串> for <字符串>)

这种形式的substring使用SQL正则表达式，第三个参数为指定一个转义字符