Oracle regexp_substr 字符串切割转换小例两则

例一：

这是一个真实的生产洗数据的需求，每次洗数据都感觉在耍杂技，ㄟ(▔皿▔ㄟ) (╯▔皿▔)╯。
需要将一个格式的中文字符串转换成另外一个格式的中文字符串，
原中文串为：
‘abc@123.com原借出者XX,基于您Apr 29, 2016在YYY申请3000.0元借款而产生的对您的第Z期债权（本金428.57元，利息37.5元），已被原出借者转让予CCC，特此告知。’
转换后的中文串为：
‘尊敬的用户（abc@123.com)，您2016年4月29日在YYY申请的3000.0元借款产生的第Z期债权（本金428.57元，利息37.5元），已被出借人（XX）转让给CCC。特此通知。’

使用shell实现：

echo 'abc@123.com原借出者XX,基于您Apr 29, 2016在YYY申请3000.0元借款而产生的对您的第Z期债权（本金428.57元，利息37.5元），已被原出借者转让予CCC，特此告知。'>test
cat test |awk -F'原借出者|,基于您|在YYY申请|元借款而产生的对您的第|期债权|，已被原出借者转让予|，特此告知。' \
'{print "尊敬的用户（"$1")，您"$3"在YYY申请的"$4"元借款产生的第"$5"期债权"$6"，已被出借人（"$2"）转让给"$7"。特此通知。"}'
# 简单的使用awk，无法将日期转换，需要捕获Apr开始的日期进行转换
# 本例不在继续做awk下一步实现

使用SQL转换样例：

with tmp as (
select 
  'abc@123.com原借出者XX,基于您Apr 29, 2016在YYY申请3000.0元借款而产生的对您的第Z期债权（本金428.57元，利息37.5元），已被原出借者转让予CCC，特此告知。 ' content 
  from dual)
select content,
       '尊敬的用户（' || regexp_substr(content, '[^原]+', 1, 1) || ')，您' ||
              regexp_substr(regexp_substr(content, '[^您|在]+', 1, 2),'[^ |,]+',1,3)||'年'||
       decode(regexp_substr(regexp_substr(content, '[^您|在]+', 1, 2),'[^ |,]+',1,1),
       'Jan', '1', 'Feb', '2', 'Mar', '3', 'Apr', '4',
       'May', '5', 'Jun', '6', 'Jul', '7',
       'Aug', '8', 'Sep', '9', 'Oct', '10',
       'Nov', '11', 'Dec', '12')||'月'||
       regexp_substr(regexp_substr(content, '[^您|在]+', 1, 2),'[^ |,]+',1,2)||'日' || '在YYY申请的' ||
       regexp_substr(content, '[^请|元]+', 1, 2) || '元借款产生的第' ||
       regexp_substr(content, '[^第|期]+', 1, 2) || '期债权' ||
       regexp_substr(content, '[^权|已]+', 1, 2) || '已被出借人（' ||
       regexp_substr(content, '[^者|,]+', 1, 2) || '）转让给' ||
       regexp_substr(content, '[^予|特]+', 1, 2) || '特此通知。' as transform_content
  from tmp;

相关解释：

• 切出相应的 ‘Apr 29, 2016’ 日期，然后使用decode函数进行月份的转换
• 字符串 ‘1a2b3c4ab5bc6ca7abc’ 切出对应的数字，只需要指定数字前后的最近一个字符作为分隔符即可，第几列则需要看该数字前后最近一个字符出现过了几次：

select '1a2b3c4ba5cb6ca7bca' str,
       regexp_substr('1a2b3c4ba5cb6ca7bca', '[^a]+', 1, 1) "num: 1"
  from dual;
--数字1前后字符只有a，指定a作为分隔符

select '1a2b3c4ba5cb6ca7bca' str,
       regexp_substr('1a2b3c4ba5cb6ca7bca', '[^c|b]+', 1, 3) "num: 4"
  from dual;
--数字4前后字符有c和b，指定c和b作为分隔符
--指定c或者b作为分隔符，那么数字4是第三列
--即数字4之前，分隔符c或者b加起来出现了2次，数字4是第3列

select '1a2b3c4ba5cb6ca7bca' str,
       regexp_substr('1a2b3c4ba5cb6ca7bca', '[^b|c]+', 1, 5) "num: 6"
  from dual;
--数字6前后字符有b和c，指定b和c作为分隔符
--指定b或者c作为分隔符，那么数字6是第五列
--即数字6之前，分隔符b或者c加起来出现了5次，第4次和第5次是俩分隔符同时出现，
--因此数字6是第5列

• 剩下的就是使用字符串拼接，将对应的中文拼接出来即可

例二：

上一个例子使用到的切串方法，再举一个稍微变化的例子，加深印象，
需求：待处理字符串为 ‘1a22b333c4444ba55555cb666666ca7777777bca’ ，任何字母都是切割符，将该字符串每一列都切出来，然后进行行列转换。

select regexp_replace('1a22b333c4444ba55555cb666666ca7777777bca',
                      '[[:alpha:]]+', ',')
  from dual;
-- 将任何连续的字母转换成分隔符逗号

select regexp_replace('1a22b333c4444ba55555cb666666ca7777777bca',
                      '[^0-9]+', ',')
  from dual;
-- 将任何连续的非数字转换成分隔符逗号

select length(regexp_replace(regexp_replace('1a22b333c4444ba55555cb666666ca7777777bca',
                                            '[^0-9]+', ','),
                             '[^,]+', '')) + 1
  from dual;
-- 将连续的非数字换成分隔符逗号，然后将非逗号的字符删除，最后求出多少个逗号，即多少列
-- 需要加1，因为串最后一个字符有可能不是分隔符，如：1，2，3，和 1，2，3

select *
  from (select regexp_replace('1a22b333c4444ba55555cb666666ca7777777bca',
                              '[^0-9]+', ',') str from dual) tab_separator,
       (select level lvl from dual
        connect by level <= 
          length(regexp_replace(regexp_replace('1a22b333c4444ba55555cb666666ca7777777bca',
                                                                 '[^0-9]+', ','),
                                                  '[^,]+', '')) + 1) tab_len;
-- 表 tab_separator 是将串分隔符化处理
-- 表 tab_len 是求出分割出来之后有几列，注意加1的操作
-- 两表笛卡尔机就能看到《Oracle regexp_substr函数简摘》样例三的推演一的显示结果了

select regexp_substr(str, '[^,]+', 1, lvl)
  from (select regexp_replace('1a22b333c4444ba55555cb666666ca7777777bca',
                              '[^0-9]+', ',') str from dual) tab_separator,
       (select level lvl from dual
        connect by level <= 
          length(regexp_replace(regexp_replace('1a22b333c4444ba55555cb666666ca7777777bca',
                                                                 '[^0-9]+', ','),
                                                  '[^,]+', '')) + 1) tab_len
 where regexp_substr(str, '[^,]+', 1, lvl) is not null;
-- 根据《Oracle regexp_substr函数简摘》样例三的继续推演，最终得出SQL
-- 需要注意where条件中的 is not null 过滤操作
-- 因为我们无法确定最后一个字符是不是分隔符，而加了1
-- 如果最后一个字符不是分割符，那么最后一行切出来就是空，过滤掉即可

[TOC]