前两天朋友提了一个和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发给我,我会补充今天的这个主题,通过一个需求,了解不同数据库中的实现,比较有意思,而且是涨姿势的一件事儿,欢迎各位,给我后台留言。