oracle使用regexp_replace实现金额格式统一

浏览: 2287

背景

1个月前有个社区的网友在微信群里问下面格式的金额如何统一成数字,

Clipboard Image.png

当时并没有想出来很方便的方法,就用replace的非常笨的办法,把所有的字符用ascii穷举出来,然后替换为空

我们都知道regexp_replace这个函数是replace的进阶版本,用法和replace基本一样,但是

regexp_replace允许使用POSIX 正则表达式来匹配

regexp_replace


Purpose

REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. By default, the function returns source_char with every occurrence of the regular expression pattern replaced with replace_string. The string returned is in the same character set as source_char. The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm

说到底还是自己对正则表达式不够6,今天查资料查到一个很方便的关键字:[[:alpha:]]可以直接匹配所有的字符

实验

with ltz as
(
select '12.3元' as amount from dual union all
select '12元' from dual union all
select '人民币12元' from dual union all
select '人民币12' from dual union all
select '12' from dual union all
select '十二元' from dual
)
select t.*,regexp_replace(t.amount,'[[:alpha:]]') as result from ltz t

Clipboard Image.png


需求二:手机号码替换成中间6位星号,手机号码规则:11位数字,且第一位为1

with ltz as
(select '老头子18665801235' n from dual union all
select 'Azzo18665801236' from dual union all
select '老子18665801237头' from dual union all
select '_18665801235chen' from dual union all
select '_28665801235chen' from dual union all
select '1A8B6C6D5E8F0G5235chen' from dual union all
select '辣条186' from dual )
select n,
case when (substr(regexp_replace(n,'[[:alpha:][:punct:]]',''),1,1) = 1
  and length(regexp_replace(n,'[[:alpha:][:punct:]]','')) = 11
  and instr(n,regexp_replace(n,'[[:alpha:][:punct:]]',''))<> 0)
  then substr(n,1,instr(n,regexp_replace(n,'[[:alpha:][:punct:]]',''))+2)||'******'||
substr(n,instr(n,regexp_replace(n,'[[:alpha:][:punct:]]',''))+9)
    else n
  end as result
 from ltz

Clipboard Image.png


需求三:去掉中文、数字、标点

with ltz as (
select 'laotouzi真帅' as t from dual union all
select 'laotouzizhen shuai!' from dual union all
select '老头子zhenshuai' from dual union all
select '老tou子真帅' from dual union all
select '老头子真帅!!!a' from dual union all
select '老头子123真帅hehe!!!' from dual 
)
select regexp_replace(regexp_replace(asciistr(t),'\\[[:alnum:]]{4}'),'[[:punct:][:digit:]]') from ltz

Clipboard Image.png


知识拓展

Oracle使用POSIX标准的正则表达式,下面是部分字符集的标准,供参考

Clipboard Image.png

From: http://www.regular-expressions.info/posixbrackets.html

推荐 10
本文由 老头子 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

8 个评论

陈老师好棒!!!!!!
。。。
大神想看结果的截图
结果就是第一个图的第二列
哦哦 谢谢大神~
特别6
卧槽 用到场景挺多的 真6
这篇文章真的很实用,收下了,多看看,对我帮助很大啊

要回复文章请先登录注册