Oracle自定义函数对文本中电话号码脱敏

weixin_41387613

于 2023-03-27 16:53:29 发布

阅读量385

点赞数

文章标签： oracle 数据库 java

本文链接：https://blog.csdn.net/weixin_41387613/article/details/129798917

版权

在这里插入图片描述

create or replace FUNCTION  tuomin(text_1 in varchar2) RETURN VARCHAR2 IS
  res VARCHAR2(4000);
begin
  res := text_1;
  for cryptic_rec in (SELECT REGEXP_SUBSTR(text_1,
                                           '((\d{11})|^((\d{7,8})|(\d{4}|\d{3})-(\d{7,8})|(\d{4}|\d{3})-(\d{7,8})-(\d{4}|\d{3}|\d{2}|\d{1})|(\d{7,8})-(\d{4}|\d{3}|\d{2}|\d{1}))$)',
                                           1,
                                           LEVEL,
                                           'i') AS STR
                        FROM DUAL
                      CONNECT BY REGEXP_SUBSTR(text_1,
                                               '((\d{11})|^((\d{7,8})|(\d{4}|\d{3})-(\d{7,8})|(\d{4}|\d{3})-(\d{7,8})-(\d{4}|\d{3}|\d{2}|\d{1})|(\d{7,8})-(\d{4}|\d{3}|\d{2}|\d{1}))$)',
                                               1,
                                               LEVEL,
                                               'i') is not null
                      
                      ) loop
    res := replace(res,
                 cryptic_rec.str,
                 substr(cryptic_rec.str, 1, 3) || '****' ||
                 substr(cryptic_rec.str, 8));
  end loop;
  return res;
end;