SQL中正则表达式的运用

一、SQL中关于正则表达式的函数

1.1、REGEXP_LIKE 匹配：REGEXP_LIKE(String, Regexp)

举例：

SELECT first_name, last_name FROM employees WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

查询结果：

FIRST_NAME	LAST_NAME
Steven	King
Steven	Markle
Stephen	Stiles

1.2、REGEXP_INSTR 包含

Oracle数据库中的REGEXP_INSTR函数的语法是：

REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_option [, match_parameter ] ] ]  ] )

source_char：

搜索值的字符表达式，可以是任何数据类型CHAR，VARCHAR2，NCHAR，NVARCHAR2，CLOB或NCLOB的。

pattern：

正则表达式

position：

可选。搜索在字符串中的开始位置。如果省略，则默认为1，这是字符串中的第一个位置。

occurrence：

可选。它是模式字符串中的第n个匹配位置。如果省略，默认为1。

return_option：

可选 指定Oracle返回的位置：

如果指定0，那么Oracle将返回出现的第一个字符的位置。这是默认的。

如果指定1，则Oracle返回字符之后发生的位置。

match_parameter：

可选。它允许你修改REGEXP_INSTR功能匹配的行为。它可以是以下的组合：

Value	Description
‘c’	区分大小写的匹配.
‘i’	不区分大小写的匹配.
‘n’	Allows the period character (.) to match the newline character. By default, the period is a wildcard.
‘m’	expression is assumed to have multiple lines, where ^ is the start of a line and $ is the end of a line, regardless of the position of those characters in expression. By default, expression is assumed to be a single line.
‘x’	Whitespace characters are ignored. By default, whitespace characters are matched like any other character.

举例：

下面看一个最简单的情况，找到字符串中的第一个”e”字的位置。

SELECT REGEXP_INSTR ('hello itmyhome', 'e')
FROM dual; 

-- Result: 2

1.3、REGEXP_REPLACE 替换

语法：

 regexp_replace(source, pattern, replace_string, occurrence)

参数：

source : string类型，要替换的原始字符串

pattern : string类型常量，要匹配的正则模式，pattern为空串时抛异常。

replace_string :string，将匹配的pattern替换成的字符串。

occurrence : bigint类型常量，必须大于等于0，
大于0：表示将第几次匹配替换成replace_string，
等于0：表示替换掉所有的匹配子串。
其它类型或小于0抛异常。

返回值：

将source字符串中匹配pattern的子串替换成指定字符串后返回，当输入source, pattern, occurrence参数为NULL时返回NULL，若replace_string为NULL且pattern有匹配，返回NULL，replace_string为NULL但pattern不匹配，则返回原串。

举例：

1、用’#‘替换字符串中的所有数字

SELECT regexp_replace('01234abcde56789','[0-9]','#') AS new_str FROM dual;

结果：#####abcde#####

1.4、REGEXP_SUBSTR 提取

语法：

REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

参数：

__srcstr ：需要进行正则处理的字符串

__pattern ：进行匹配的正则表达式

__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）

__occurrence ：标识第几个匹配组，默认为1

__modifier ：模式（'i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’。）

二、关于正则表达式

正则表达式常用字符：

字符	说明
^	匹配字符串的开始位置，若用在中括号中[ ] 时，表示不匹配括号中字符串
$	匹配字符串的末尾，若设置了表达式对象的Multiline属性，则也匹配’/n’或’/r’
.	匹配单个字符
*	匹配前面子表达式 0次或多次（至少有一次）
?	匹配前面子表达式 0 次或 1 次 （至多有一次）
+	匹配前面子表达式 1 次或更多次
{m}	匹配 m 次
{m,}	至少匹配 m 次
{m, n}	至少匹配 m 次但不超过 n 次
()	匹配括号中全部字符
[]	匹配括号中一个字符，范围描述，如[0-9] [a-z] [A-Z]
{}	用于限定匹配次数，如 {n}表示匹配n个字符，{n,}表示至少匹配n个字符，{n,m}表示至少n个最多m个
\	转义字符，如上基本符号匹配都需要转义字符 ，如 * 表示匹配*号
\w	表示英文字母和数字
\W	表示非字母和数字
\d	表示数字
\D	表示非数字

常用的正则表达式：

匹配由26个英文字母组成的字符串： ^[A-Za-z]+$

匹配由26个英文字母的大写组成的字符串： ^[A-Z]+$

匹配由26个英文字母的小写组成的字符串： ^[a-z]+$

匹配由数字和26个英文字母组成的字符串： ^[A-Za-z0-9]+$

匹配由数字和26个英文字母或者下划线组成的字符串： ^\w+$

匹配整数： ^-?[1-9]\d*$

匹配正整数： ^[1-9]\d*$

匹配负整数： ^-[1-9]\d*$

匹配非负整数： ^[1-9]\d*|0$

匹配非正整数： ^-[1-9]\d*|0$

电子邮箱： ^([a-z0-9_\.-]+)@([\da-z\.-]+)\.([a-z\.]{2,6})$

URL	： ^(https?:\/\/)?([\da-z\.-]+)\.([a-z\.]{2,6})([\/\w \.-]*)*\/?$

IP地址：	^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$

HTML标签： ^<([a-z]+)([^<]+)*(?:>(.*)<\/\1>|\s+\/>)$

SQL语句： ^(select|drop|delete|create|update|insert).*$

邮政编码： ^[1-9]\d{5}(?!\d)$

Unicode编码中文字符串： ^[u4e00-u9fa5],{0,}$

空白行： \n[\s| ]*\r

首尾空格： (^\s*)|(\s*$)

双字节字符：	[^\x00-\xff]