拼接字符串SQL需求

同事提了个需求，表中一个字段，存储格式例如abc_x_cd，需要通过SQL拼接出另外一个值，例如abc_x_cd abc x cd，即根据原始值，按照"_"分割，按照每个部分，再通过空格，和原始值拼接。

MySQL可以通过split(column，'_') as column1，然后concat(column,' '，column1) as newcolumn得到值。

但是在Oracle，没有split函数，如何实现需求？

创建测试表，

create table t (c1 varchar2(25));
insert into t values('abc_x_cd');
select * from t;

方案1

利用正则regexp_substr和listagg，能实现这个需求，

select c1 || ' ' || c2
from t, (select listagg(regexp_substr(c1, '[^_]+', 1, rownum),' ') within group (order by rownum) as c2 from t
connect by rownum <= length(c1) - length(replace(c1, '_', ''))+1) a;

regexp_substr定义如下，

REGEXP_SUBSTR(source_char, pattern
              [, position
                 [, occurrence
                    [, match_param
                       [, subexpr
]
                    ]
                 ]
              ]
             )

针对值abc_x_cd，

regexp_substr(c1, '[^_]+', 1, rownum)得到的是"abc"、"x"、"cd"。

connect by rownum <= length(c1) - length(replace(c1, '_', ''))+1，循环次数=几个分隔符+1。

利用listagg做列转行，设置within group (order by rownum)保证按照行的顺序拼接成"abc x cd"。

再通过||和原始字段进行拼接，就得到了"abc_x_cd abc x cd"。

方案2

方案1的思路，其实是按照MySQL的split函数的逻辑考虑的，按照某个字符进行分隔，分隔出的几个字符串再通过列转行进行拼接，但其实这就进入了一种定势思维。

再回到需求，需要根据"abc_x_cd"得到"abc_x_cd abc x cd"，其实就是怎么从"abc_x_cd"得到"abc x cd"，这个不就是将"_"，替换成" "？

只需要使用replace函数，就可以实现，

select c1 || ' ' || replace(c1, '_', ' ') from t;

因此，对待一个问题，有时候需要发散一下，有时候需要回到需求原点，这就相当于你要翻过一扇大门的时候，发现钥匙就在兜里装着。