字符转换的SQL需求增强

朴灿烈づ我的快乐病毒、 2021-11-02 03:24 176阅读 0赞

上次《[字符转换的SQL需求][SQL]》讨论的需求，使用各种函数，实现了字符转换的需求，但通过朋友指教，其实存在些问题。

这是原来的写法，

SQL> SELECT listagg(x.a, ';') within GROUP (ORDER BY x.a) FROM
      2  (SELECT substr(regexp_substr(a, '[^,]+',1,level),3,7)||'.'||substr(regexp_substr(a, '[^,]+',1,level),0,2) AS a
      3  FROM test
      4  connect by level <= length(decode(substr(a,-1),',',substr(a,1,length(a)-1),a)) - length(REPLACE(a,',',''))+1) x;
    
    LISTAGG(X.A,';')WITHINGROUP(ORDERBYX.A)
    --------------------------------------------------------
    000001.SH;000002.SZ;000003.SZ

尚大师给出了regexp\_replace和regexp\_count的替代写法，

select listagg(regexp_replace(x.a, '([A-Z]{2})([0-9]{6})', '\2.\1'), ';') within group(order by x.a)from (select regexp_substr(a, '[^,]+', 1, level) afrom testconnect by level <= regexp_count(a, '[^,]+')) x;listagg(regexp_replace(x.a, '([A-Z]{2})([0-9]{6})', '\2.\1'), ';') within group(order by x.a)
    from (select regexp_substr(a, '[^,]+', 1, level) a
    from test
    connect by level <= regexp_count(a, '[^,]+')) x;

因为当时只测了一条数据的场景，所以用这种方法，对多条数据，是会有问题的，如下所示，test存在两条数据，执行SQL，并不是我们需要的结果，无法保证汇总后的顺序，

SQL> SELECT * FROM test;A------------------------------SH000001,SZ000002,SZ000003SX000001,SX000002,SX000003SQL> SELECT listagg(x.a, ';') within GROUP (ORDER BY x.a) FROM  2  (SELECT substr(regexp_substr(a, '[^,]+',1,level),3,7)||'.'||substr(regexp_substr(a, '[^,]+',1,level),0,2) AS a  3  FROM test  4  connect by level <= length(decode(substr(a,-1),',',substr(a,1,length(a)-1),a)) - length(REPLACE(a,',',''))+1) x;LISTAGG(X.A,';')WITHINGROUP(ORDERBYX.A)--------------------------------------------------------------------------------000001.SH;000001.SX;000002.SX;000002.SX;000002.SZ;000002.SZ;000003.SX;000003.SX;000003.SX;000003.SX;000003.SZ;000003.SZ;000003.SZ;000003.SZFROM test;
    A
    ------------------------------
    SH000001,SZ000002,SZ000003
    SX000001,SX000002,SX000003
    
    SQL> SELECT listagg(x.a, ';') within GROUP (ORDER BY x.a) FROM
      2  (SELECT substr(regexp_substr(a, '[^,]+',1,level),3,7)||'.'||substr(regexp_substr(a, '[^,]+',1,level),0,2) AS a
      3  FROM test
      4  connect by level <= length(decode(substr(a,-1),',',substr(a,1,length(a)-1),a)) - length(REPLACE(a,',',''))+1) x;
    
    LISTAGG(X.A,';')WITHINGROUP(ORDERBYX.A)
    --------------------------------------------------------------------------------
    000001.SH;000001.SX;000002.SX;000002.SX;000002.SZ;000002.SZ;000003.SX;000003.SX;
    000003.SX;000003.SX;000003.SZ;000003.SZ;000003.SZ;000003.SZ

尚大师给出了一种解法，很酷炫，这些地儿我还得再学学，引入了rownum做层级，并用rowid进行聚类，目的应该是保证同一个rowid的转换后还是同一行，很巧妙，

SQL> select listagg(regexp_replace(xx.a, '([A-Z]{2})([0-9]{6})', '\2.\1'), ';') within group(order by xx.rn) a  2  from (select x.rowid rid, regexp_substr(a, '[^,]+', 1, y.rn) a, y.rn  3  from test x, (select rownum rn from dual connect by rownum <= 5) y  4  where y.rn <= regexp_count(x.a, '[^,]+')) xx  5  group by rid;A--------------------------------------------------------------------------------000001.SH;000002.SZ;000003.SZ000001.SX;000002.SX;000003.SX'([A-Z]{2})([0-9]{6})', '\2.\1'), ';') within group(order by xx.rn) a
      2  from (select x.rowid rid, regexp_substr(a, '[^,]+', 1, y.rn) a, y.rn
      3  from test x, (select rownum rn from dual connect by rownum <= 5) y
      4  where y.rn <= regexp_count(x.a, '[^,]+')) xx
      5  group by rid;
    A
    --------------------------------------------------------------------------------
    000001.SH;000002.SZ;000003.SZ
    000001.SX;000002.SX;000003.SX

又碰见两个新的函数，

1. REGEXP\_REPLACE函数，

`REGEXP_REPLACE` extends the functionality of the `REPLACE` function by letting you search a string for a regular expression pattern. By default, the function returns `source_char` with every occurrence of the regular expression pattern replaced with `replace_string`. The string returned is in the same character set as `source_char`. The function returns `VARCHAR2` if the first argument is not a LOB and returns `CLOB` if the first argument is a LOB.

2. REGEXP\_COUNT函数，

`REGEXP_COUNT` complements the functionality of the `REGEXP_INSTR` function by returning the number of times a pattern occurs in a source string. The function evaluates strings using characters as defined by the input character set. It returns an integer indicating the number of occurrences of `pattern`. If no match is found, then the function returns 0.

另外，朱大师给出了PG的解法，

![640?wx\_fmt=png][640_wx_fmt_png]

我只有EDB的库，虽然是PG的企业版，但这种写法，好像不支持，

![640?wx\_fmt=png][640_wx_fmt_png 1]

黄老师则指出，用Oracle的SQL可以直接在达梦中跑。

谢谢各位朋友，若还有好的建议，欢迎随时留言，谢谢。

[SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247489170&idx=1&sn=a6d9f6ac39ee29a05be78101a7540677&chksm=ea815361ddf6da77bd082da96b8f09a238ae5969ebd674763408d8936a37d315983c6b6183e5&scene=21#wechat_redirect
[640_wx_fmt_png]: /images/20211102/32699087df054e97a6696f6bde44fe9b.png
[640_wx_fmt_png 1]: /images/20211102/bdadb7706bcf4eb7b1537be91dd52260.png