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

发表: 2016-11-22 浏览: 3590

Oracle 数据库

背景

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