字符转换的SQL需求增强

bisal

发布于 2019-08-15 19:46:38

60300

代码可运行

文章被收录于专栏：bisal的个人杂货铺bisal的个人杂货铺

运行总次数：0

代码可运行

上次《字符转换的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的解法，