Oracle处理以,隔开存储的数据

以,隔开的字符串。我们需要管理这个字符串，使其中包含某个测点标识的数据去掉，你会如何做呢（思考一会）？

一开始想到的是字符串替换，后面发现这种方式不好，有异常情况发生（某个测点标识包含另一个测点标识时会发生异常），而且要考虑的情况比较多。

这种方式不行，那更好的方式是什么？

从数据存储的结构上来看，我们最好的存储方式是一个测点标识一条记录，这样就可以区分开了，既然这样我们如何将数据分割开来呢？

一个一个找？是的，其实就是这样。

例:a,b,c,d

首先找到a，接着找到b，依次下去找到最后一个d

首先分析数据的结构（多种角度）：

1、a 第一个逗号 b 第二个逗号 c 第三个逗号 d

2、非逗号 逗号非逗号逗号非逗号非逗号

3、第一个非逗号 逗号第二个非逗号逗号第三个非逗号逗号第四个非逗号

。。。。

数据始终是有规律的，数据有两个格式：逗号和非逗号，同时它们都有位置信息。

我们可以结合这个信息找到其中的信息。

Oracle中有个函数：

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

__srcstr     ：需要进行正则处理的字符串

__pattern    ：进行匹配的正则表达式

__position   ：起始位置，从第几个字符开始正则表达式匹配（默认为1）

__occurrence ：标识第几个匹配组，默认为1

__modifier   ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

其中的pattern和occurrence是这个问题求解的关键所在。

模拟这个过程：

SELECT REGEXP_SUBSTR('a,b,c,d','[^,]+',1,1)AS STRFROM DUAL;  

SELECT REGEXP_SUBSTR('a,b,c,d','[^,]+',1,2)AS STRFROM DUAL;

SELECT REGEXP_SUBSTR('a,b,c,d','[^,]+',1,3)AS STRFROM DUAL;  

SELECT REGEXP_SUBSTR('a,b,c,d','[^,]+',1,4)AS STRFROM DUAL;

 

上面这些sql，唯一不同的就是匹配的是第几个匹配组。

那我们能不能通过辅助表产生这些递增的匹配组索引信息，当然能

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7;  

产生1-7的递增索引，结合上面的例子来做

 

这样会有额外的空数据，那么我们需要确定到底有少个逗号

LENGTH('a,b,c,d')-LENGTH(REPLACE('a,b,c,d',',',''))+1

 

结合到我们的业务上，需要移除指定测点标识的数据，先拆分成多行，再通过指定的测点标识进行过滤，最后通过wm_concat函数重组剩下满足的测点标识

SELECT  WM_CONCAT(PJCDDM) AS PJCDDM

   FROM  

   (SELECT SUBDY.XH,REGEXP_SUBSTR(SUBDY.PJCDDM,'[^,]+',1,L) AS  PJCDDM,  

       FROM T_JCSJZX_PJFW_DBSDY SUBDY,  

       (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL<=150)  

          WHERE L<=LENGTH(SUBDY.PJCDDM) -LENGTH(REPLACE(SUBDY.PJCDDM,','))+1                ORDER BY SUBDY.PJCDDM  

      ) DY WHERE DY.PJCDDM !=  过滤的测点 AND DY_MAIN.XH = DY.XH GROUP BY DY.XH  

 

注：mysql中同样的思路只是查找数据的函数(substring_index)稍有不同而已

 

最终sql:

UPDATE T_JCSJZX_PJFW_DBSDY DY_MAIN SET(PJCDDM,PJCDMC) = (

       SELECT  WM_CONCAT(PJCDDM) AS PJCDDM, WM_CONCAT(PJCDMC) AS PJCDMC

       FROM

       (SELECT SUBDY.XH,REGEXP_SUBSTR(SUBDY.PJCDDM,'[^,]+',1,L) AS  PJCDDM,

               REGEXP_SUBSTR(SUBDY.PJCDMC,'[^,]+',1,L) AS  PJCDMC

               FROM T_JCSJZX_PJFW_DBSDY SUBDY,

               (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL<=150)

               WHERE L<=LENGTH(SUBDY.PJCDDM) -LENGTH(REPLACE(SUBDY.PJCDDM,','))+1

               ORDER BY SUBDY.PJCDDM,SUBDY.PJCDMC

        ) DY WHERE DY.PJCDDM != '530700FB2501000201' AND DY_MAIN.XH = DY.XH GROUP BY DY.XH

) WHERE INSTR(','||DY_MAIN.PJCDDM||',', ','||'530700FB2501000201'||',') > 0