字符转换的SQL需求

- 日理万妓 2021-11-17 14:54 257阅读 0赞

前两天朋友提了一个和SQL有关的问题，准确地说，是和字符串转换有关的，首先我们创建测试表，用模拟数据进行说明，

SQL> CREATE TABLE test(a VARCHAR2(30));Table created.SQL> INSERT INTO test VALUES ('SH000001,SZ000002,SZ000003');1 row created.SQL> COMMIT;Commit complete.SQL> SELECT * FROM test;A------------------------------SH000001,SZ000002,SZ000003 CREATE TABLE test(a VARCHAR2(30));
    Table created.
     SQL> INSERT INTO test VALUES ('SH000001,SZ000002,SZ000003');
    1 row created.
     SQL> COMMIT;
    Commit complete.
     SQL> SELECT * FROM test;
    A
    ------------------------------
    SH000001,SZ000002,SZ000003

表中字段a的值是'SH000001,SZ000002,SZ000003'，需求是将其转为'000001.SH;000002.SZ;000003.SZ'。

我们翻译下需求，

1.每个字段原始用","进行分割，包含多个部分，需要转换为用“;“进行分割，每个部分的位置不变。

2.原始字符串中，","分割的每个部分，需要从xxdddddd转换为dddddd.xx。

我们以Oracle 11g为测试库，有几种的想法，

想法1.如果记录有限，一种简单的方法，可以采用穷举，利用substr函数，拼接出检索结果，功能上可以实现，但是扩展性，可能会差一些。

想法2.将需求进行拆分，单步执行，

(1). 先将原始字符串，按照","分隔，拆出每个部分。

(2). 遍历(1)拆出的字符串进行转换，从xxdddddd转换为dddddd.xx。

(3). 将(1)和(2)的结果集，进行拼接，使用";"分隔，得到需要的，

"dddddd.xx;dddddd.xx;dddddd.xx"。

我们看下想法2的实现，

(1). 先将原始字符串，按照","分割，拆出每个部分。

参考《[字段拆分多行的需求][Link 1]》，利用正则函数regexp\_substr(a, '\[^,\]+',1,level)，拆出","分隔的字符串，通过connect by level，实现行转列，此时中间结果的行数等于原始字符串","分隔的组数。

(2). 遍历(1)拆出的字符串进行转换，从xxdddddd转换为dddddd.xx。

因为xxdddddd中的xx和dddddd，位置是固定的，所以用substr解析，通过||'.'||连接字符串，此时中间结果集，就是需要的dddddd.xx。

(1)和(2)的中间结果集，如下所示，

SQL> SELECT substr(regexp_substr(a, '[^,]+',1,level),3,7)||'.'||substr(regexp_substr(a, '[^,]+',1,level),0,2) AS a  2  FROM test  3  connect by level <= length(decode(substr(a,-1),',',substr(a,1,length(a)-1),a)) - length(REPLACE(a,',',''))+1;A-------------------------------------000001.SH000002.SZ000003.SZ'[^,]+',1,level),3,7)||'.'||substr(regexp_substr(a, '[^,]+',1,level),0,2) AS a
      2  FROM test
      3  connect by level <= length(decode(substr(a,-1),',',substr(a,1,length(a)-1),a)) - length(REPLACE(a,',',''))+1;
    
    A
    -------------------------------------
    000001.SH
    000002.SZ
    000003.SZ

(3). 将(1)和(2)的结果集，进行拼接，使用";"分隔。

参考《[11g中利用listagg函数实现自动拼接INSERT语句][11g_listagg_INSERT]》，通过listagg函数，将每行的dddddd.xx，用";"拼接，得到需要的结果，如下所示，

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
      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

为了实现上面的需求，用了不少函数的，listagg、substr、regexp\_substr、connect by level、length、decode等，但是有些函数，可能是Oracle特有的，如果使用MySQL实现相同的需求，listagg函数可以用group\_concat替代，regexp\_substr在MySQL 5.x中是不支持的，而且connect by level是没有直接能用的，据说需要自定义函数来实现，所以在Oracle中能跑的SQL不能直接在其他数据库用，因此，如果朋友们对MySQL比较熟悉，甚至是PG或者其他各种数据库，可以根据需求，写个测试SQL发给我，我会补充今天的这个主题，通过一个需求，了解不同数据库中的实现，比较有意思，而且是涨姿势的一件事儿，欢迎各位，给我后台留言。

[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247486632&idx=1&sn=b439954e3c677c7f168737d60c3b453f&chksm=ea81495bddf6c04df1d846ec1e64fa970f8958b5c9979b81aeab790b88a5f8fc37ade505adf4&scene=21#wechat_redirect
[11g_listagg_INSERT]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247483765&idx=1&sn=d90d7d1a8ae31f5e3b0a897269ddbd12&chksm=ea814486ddf6cd9064712116f926123550d4830aed4b54c6a96618988ae6f03085733d59fb5c&scene=21#wechat_redirect