如何处理字典表映射的字段中有逗号

/ 技术金融业务 / 104浏览
场景

今天下班前有个同事喊我帮忙看下,带逗号的字段怎么用字典表映射,如下图效果
image-20210112163501499

我的思路是先把带逗号的字段转成多行,然后再映射,于是先拿 XSHG,XSHE 测试是否可行

SELECT REGEXP_SUBSTR ('XSHG,XSHE', '[^,]+', 1,rownum) AS m_type
 FROM dual
 CONNECT BY ROWNUM <=
LENGTH ('XSHG,XSHE') - LENGTH (REPLACE ('XSHG,XSHE', ',', ''))+1

image-20210104211614094

发现可以,再加上映射字典表,转换关系

select * from ( SELECT REGEXP_SUBSTR ('XSHG,XSHE', '[^,]+', 1,rownum) AS m_type
 FROM dual
 CONNECT BY ROWNUM <=
LENGTH ('XSHG,XSHE') - LENGTH (REPLACE ('XSHG,XSHE', ',', ''))+1) T
left join (select T.KEY,T.VALUE from dict t) D
ON D.KEY=T.m_type;

image-20210104211638089

再用wm_concat 函数拼接回返回就可以了

select wm_concat(d.value) from ( SELECT REGEXP_SUBSTR ('XSHG,XSHE', '[^,]+', 1,rownum) AS m_type
 FROM dual
 CONNECT BY ROWNUM <=
LENGTH ('XSHG,XSHE') - LENGTH (REPLACE ('XSHG,XSHE', ',', ''))+1) T
left join (select T.KEY,T.VALUE from dict t) D
ON D.KEY=T.m_type;

image-20210104211734816

封装成函数

CREATE OR REPLACE FUNCTION GET_FRO_MARKETS(MARKETS VARCHAR2)
  RETURN VARCHAR2 IS
  MARKETS_VALUE VARCHAR(50);
BEGIN
  SELECT WM_CONCAT(D.VALUE) AS M_TYPE
  INTO MARKETS_VALUE
  FROM (SELECT REGEXP_SUBSTR(MARKETS, '[^,]+', 1, ROWNUM) AS M_TYPE
        FROM DUAL
        CONNECT BY ROWNUM <=
                   LENGTH(MARKETS) -
                   LENGTH(REPLACE(MARKETS, ',', '')) + 1) T
  LEFT JOIN (SELECT T.KEY,
                    T.VALUE
             FROM DICT T) D
  ON D.KEY = T.M_TYPE;
  RETURN MARKETS_VALUE;
END;

测试后结果:

SELECT T.ACCID,
       GET_FRO_MARKETS(T.MARKETS) AS MARKETS
FROM TEXT_FOR_DICT T;

image-20210104211916436

以下是测试数据:sql_for_split.sql