ORCALE - 字符串解析(一)：字符串转表格，转行，转列

需求：

以下字符串解析转表，按‘；’和‘，’隔开，组成数据来进行保存和处理

'a1,c1,,b1,a1;a2,s2,f2,a2;a32,s32,d3,f314'

分析：

按我理解，正常来说，数据库可以少量处理简单的字符串解析，大量且复杂的字符串解析操作是需要在中台服务器那边提前解决的；

但是总有历(S)史(B)原因导致数据库需要处理一些字符串解析。

如果遇到二维数组的情形，在过程里需要多段代码来进行处理，或者使用临时表等。

而本文的目的是想通过一段SQL查询 就能达到解析的目的。

实践：

在字符串分割的方法,第一反应是 substr()函数；后来在网上陆续找到了其他两种方式

1. table()函数

SELECT COLUMN_VALUE NAME FROM TABLE(SPLIT('a1,c1,,b1,a1;a2,s2,f2,a2;a32,s32,d3,f314', ';'));

 2. substr()函数 -----------（后来网上看到了现成的）

WITH T AS (SELECT ',' || 'asf85,8df6,87' || ',' RPT_ID,
               LENGTH('asf85,8df6,87' || ',') - NVL(LENGTH(REPLACE('85,86', ',')), 0) CNT
          FROM DUAL) 
SELECT SUBSTR(T.RPT_ID,
              INSTR(T.RPT_ID, ',', 1, C.LV) + 1,
              INSTR(T.RPT_ID, ',', 1, C.LV + 1) - (INSTR(T.RPT_ID, ',', 1, C.LV) + 1)
              ) AS RPT_ID
  FROM T,
       (SELECT LEVEL LV
          FROM DUAL
        CONNECT BY LEVEL <= LENGTH('85,86,87' || ',') -
                   NVL(LENGTH(REPLACE('85,86,87', ',')), 0)) C
 WHERE T.CNT >= C.LV;

 3. REGEXP_SUBSTR()函数，substr的变种，使用正则表达式来匹配字符串

SELECT REGEXP_SUBSTR('1,2,3', '[^,]+', 1, ROWNUM)
  FROM DUAL
CONNECT BY ROWNUM <=
           LENGTH('1,2,3') - LENGTH(REGEXP_REPLACE('1,2,3', ',', '')) + 1;

做了下优化（不影响效率），

SELECT REGEXP_SUBSTR(str, '[^,]+', 1, ROWNUM)
  FROM (select 'EH,EZ,E0,J3,E4,L0,J0,T1' str from dual）
CONNECT BY ROWNUM <=
           LENGTH(str) - LENGTH(REGEXP_REPLACE(str, ',', '')) + 1;

我这里选择了第三个来作为展开，使用正则表达式的话匹配功能相对强大，如遇到其他特殊匹配字符也能适应。

--20230510-------------------------------------

回过头来，补充下，相关效率问题，

方法	时间耗费	备注
1. table()	29	SPLIT不能用于建表
2. substr()函数	4
3. REGEXP_SUBSTR()函数	2

----------------------------------------------------- 

结果：

1. 对字符串进行';' 分割，获取到3条记录 

WITH A AS (SELECT 'a1,c1,,b1,a1;a2,s2,f2,a2;a32,s32,d3,f314,asdf' S, ';' P1 , ',' P2 FROM DUAL)
,B AS(SELECT REGEXP_SUBSTR(A.S, '[^' || A.P1 || ']+', 1, ROWNUM) S, P2, rownum n
    FROM A CONNECT BY ROWNUM <=  LENGTH(A.S) - LENGTH(REGEXP_REPLACE(A.S, A.P1, '')) + 1)
select * from B;

2.  计算每条记录有多少个元素，存放L字段

,B_N1 as (select B.*,  (LENGTH(B.S) - LENGTH(REGEXP_REPLACE(B.S, B.P2, '')) + 1) L  from B) 
select * from B_N1;

 

3. 取元素最多的数量，（为下一步做准备）

,B_N as (select p2,l_max L,n,s  from B_N1  left join (select max(L) L_MAX from B_N1) on 1=1)
select * from B_N;

4.   对记录进行行列转换，（如果L的数据没有保持一致的话，会出现多条记录）

,C as (SELECT * FROM B_N pivot(max(S)  for N IN(1 as n1, 2 as n2, 3 as n3)))
select * from C;

4.  再对每个字段分别进行切割字符串

,C_N as (SELECT REGEXP_SUBSTR(C.n1, '[^' || C.P2 || ']+', 1, ROWNUM) S1,
       REGEXP_SUBSTR(C.n2, '[^' || C.P2 || ']+', 1, ROWNUM) S2,
       REGEXP_SUBSTR(C.n3, '[^' || C.P2 || ']+', 1, ROWNUM) S3
  FROM C  CONNECT BY ROWNUM <= C.L)
select * from C_N 

5. 最后再看一眼原始数据 

'a1,c1,,b1,a1;a2,s2,f2,a2;a32,s32,d3,f314'

6.附上完整代码

WITH A AS (SELECT 'a1,c1,,b1,a1;a2,s2,f2,a2;a32,s32,d3,f314,asdf' S, ';' P1 , ',' P2 FROM DUAL)
,B AS(SELECT REGEXP_SUBSTR(A.S, '[^' || A.P1 || ']+', 1, ROWNUM) S, P2, rownum n
    FROM A CONNECT BY ROWNUM <=  LENGTH(A.S) - LENGTH(REGEXP_REPLACE(A.S, A.P1, '')) + 1)
,B_N1 as (select B.*,  (LENGTH(B.S) - LENGTH(REGEXP_REPLACE(B.S, B.P2, '')) + 1) L  from B) 
,B_N as (select p2,l_max L,n,s  from B_N1  left join (select max(L) L_MAX from B_N1) on 1=1)
,C as (SELECT * FROM B_N pivot(max(S)  for N IN(1 as n1, 2 as n2, 3 as n3)))
,C_N as (SELECT REGEXP_SUBSTR(C.n1, '[^' || C.P2 || ']+', 1, ROWNUM) S1,
       REGEXP_SUBSTR(C.n2, '[^' || C.P2 || ']+', 1, ROWNUM) S2,
       REGEXP_SUBSTR(C.n3, '[^' || C.P2 || ']+', 1, ROWNUM) S3
  FROM C  CONNECT BY ROWNUM <= C.L)
select * from C_N ;

总结：

等写完后再会过去翻看，发现还是有不少的问题。

1.  比如图中圈出来的地方， 这块代码是需要动态生成的，而不是我最开始设想的一个SELECT语句解决

 2. 在解析 ‘c1,,b1’的时候，没有解析出来NULL，这个可能需要再深入了解下 REGEXP_SUBSTR()函数的机制了，或者寻找更好的替代法

废话：

 写在最后，下次准备看看JSON字符串怎么解析最优，这也是我写这篇文章的初衷，因为之前需求遇到了，当时还优化了别人的工具函数，但还是觉得意犹未尽，尚有优化的空间。