字符转换的SQL需求
前两天朋友提了一个和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). 先将原始字符串，按照","分割，拆出每个部分。
参考《字段拆分多行的需求》，利用正则函数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语句》，通过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发给我，我会补充今天的这个主题，通过一个需求，了解不同数据库中的实现，比较有意思，而且是涨姿势的一件事儿，欢迎各位，给我后台留言。