oracle值sql记录

前言
由于本人sql比较薄弱 将一些常用又使用的sql语句记录与大家分享
一：替换特殊字符分割的字符串
需求如下：
有张原始表tschedule 和新表bjcodechange ，需要将原始表的compeny_code字段和bjcodechange 的oldwindcode 进行关联替换成bjcodechange表的newwindcode字段
无特殊字符的查询很简单 如下

UPDATE tschedule  ts
   SET ts.security_code =
       (SELECT bj.newwindcode
          FROM BJcodechange bj
         WHERE ts.security_code = bj.oldwindcode
           )
 WHERE EXISTS (SELECT 1
          FROM BJcodechange bj
         WHERE ts.security_code = bj.oldwindcode);

但是有特殊字符的数据如下 注意测试原始表的compeny_code字段的内容许如下所示
tschedule：

bjcodechange：

最终的效果如下

完整步骤
步骤1：拆分后的中间数据 (split_data)

MERGE INTO tschedule a
USING (
  WITH split_data AS (
    SELECT 
      a.rowid AS rid, 
      regexp_substr(a.compeny_code, '[^,]+', 1, LEVEL) AS elem,
      LEVEL AS ord
    FROM tschedule a
    CONNECT BY 
      LEVEL <= regexp_count(a.compeny_code, ',') + 1
      AND PRIOR a.rowid = a.rowid
      AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
  )
  SELECT 
    rid,
    LISTAGG(NVL(b.newwindcode, elem), ',') WITHIN GROUP (ORDER BY ord) AS new_code 
  FROM split_data sd
  LEFT JOIN bjcodechange b ON sd.elem = b.oldwindcode  
  GROUP BY rid
) merged
ON (a.rowid = merged.rid)
WHEN MATCHED THEN
  UPDATE SET a.compeny_code = merged.new_code
WHERE a.compeny_code != merged.new_code;

好了 至此 oracle值sql记录 点点关注不迷路 老铁们！！！！！