EsgynDB2.7版本中支持三种常用的正则函数，
REGEXP_REPLACE
REGEXP_SUBSTR
REGEXP_COUNT
REGEXP_REPLACE
描述
根据正则表达式做字符串替换
语法
REGEXP_REPLACE(instr,pattern,replace_str)
示例
//将 01234 替换为 0abc
select regexp_replace('0123456789','01234','0abc') from dual;
SQL>select regexp_replace('0123456789','01234','0abc') from dual;
(EXPR)
--------------------
abc56789
--- 1 row(s) selected.
将匹配到的数字替换为’ *#
select regexp_replace('01234abcde56789','[0-9]','*#') as new_str from dual;
SQL>select regexp_replace('01234abcde56789','[0-9]','*#') as new_str from dual;
NEW_STR
------------------------------
*#*#*#*#*#abcde*#*#*#*#*#
--- 1 row(s) selected.
//将字符串中的字母去掉（小写字母）
select regexp_replace('01234abcde56789','[a-z]','') as new_str from dual;
SQL>select regexp_replace('01234abcde56789','[a-z]','') as new_str from dual;
NEW_STR
------------------------------
0123456789
--- 1 row(s) selected.REGEXP_SUBSTR
- 描述
根据正则表达式做字符串截取 - 语法
REGEXP_SUBSTR(instr,pattern[,position[,occurrence]]) - 示例
//查询使用正则分割后的第一个值，也就是17
select regexp_substr('17,20,23','[^,]+',1,1) as str from dual;
SQL>select regexp_substr('17,20,23','[^,]+',1,1) as str from dual;
STR
--------
17
--- 1 row(s) selected.
//查询使用正则分割后的最后一个值，也就是23
select regexp_substr('17,20,23','[^,]+',1,3) as str from dual;
SQL>select regexp_substr('17,20,23','[^,]+',1,3) as str from dual;
STR
--------
23
--- 1 row(s) selected.
//获取一个多个数值的列，从而能够让结果以多行的形式展示出来
select nvl(regexp_substr('17,20,23', '[^,]+', 1, level), 'null') as str from dual connect by level <= 7;
SQL>select nvl(regexp_substr('17,20,23', '[^,]+', 1, level), 'null') as str from dual connect by level <= 7;
STR
--------
17
20
23
null
null
null
null
--- 7 row(s) selected.REGEXP_COUNT
- 描述
根据正则表达式统计匹配文本出现次数 - 语法
REGEXP_COUNT (instr, pattern [, position]) - 示例
//得到字符串中ge的出现次数
select regexp_count('george','ge',1) from dual;
SQL>select regexp_count('george','ge',1) from dual;
(EXPR)
----------
2
--- 1 row(s) selected.
//得到字符串中小写字母“a”的出现次数
select regexp_count ('The pro-niece was born today, so exciting.', 'a') "Count 'a'" from dual;
SQL>select regexp_count ('The pro-niece was born today, so exciting.', 'a') "Count 'a'" from dual;
Count 'a'
----------
2
--- 1 row(s) selected.
//从第17个字符处开始检索字母a
select regexp_count ('The pro-niece was born today, so exciting!', 'a', 17) "Count 'a'" from dual;
SQL>select regexp_count ('The pro-niece was born today, so exciting!', 'a', 17) "Count 'a'" from dual;
Count 'a'
----------
1
--- 1 row(s) selected.————————————————
版权声明：本文为CSDN博主「post_yuan」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Post_Yuan/article/details/101032638
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69953058/viewspace-3124734/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Greenplum原理介绍
请登录后发表评论
登录
全部评论
