拼接字符串SQL需求

末蓝、 2022-09-05 04:13 321阅读 0赞

同事提了个需求，表中一个字段，存储格式例如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;

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

**近期更新的文章：**

《[《SQL Cookbook》 - 第二章 查询结果排序][SQL Cookbook_ - _]》

《[2021雷军年度演讲 - 我的梦想，我的选择][2021_ -]》

《[梅西 ≠ 一人一城？][Link 1]》

《[小白学习MySQL - 表空间碎片整理方法][MySQL -]》

《[Oracle字符串类型扩容隐患][Oracle]》

**文章分类和索引：**

[《公众号800篇文章分类和索引][800]》

[SQL Cookbook_ - _]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a00dddf5291b9a4dc0519cb1ccb983f74e5352b002e5de19f042114bb4c4b0f7f2690cae&idx=1&mid=2247493118&scene=21&sn=009b4e18032184e185322ee660b4a57b#wechat_redirect
[2021_ -]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a005ddf52913a1181208aece5213ba94f30d5aa4eedce0d574a399a367b5ff3b0a010e20&idx=1&mid=2247493110&scene=21&sn=54f18f198863d65e21add3e0414f7e23#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a029ddf5293fbd6d72536d566769b6ef0dff0d6f438363ece58c99c57a68d5a93e9796e1&idx=1&mid=2247493082&scene=21&sn=1adcc88366ab2baa5b2e6767941994d3#wechat_redirect
[MySQL -]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a030ddf52926dd6c3a22a7da1a3c7055d8e24e1ee958d885b0c25d29202239587a2a1a66&idx=1&mid=2247493059&scene=21&sn=16eaffcc25f1e7d50c792e58b3a230ea#wechat_redirect
[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a055ddf52943c9699cd6e4ccd7ab18c6f181921a97cf9c35cb962b9898c6eed1a8fb553f&idx=1&mid=2247493030&scene=21&sn=5bc4c2487b1ac96d507ce7468f02d535#wechat_redirect
[800]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a084ddf529920f2572e843967c4cb4248b6adc854a001315dd54264080349d3a5cc2ea14&idx=1&mid=2247492983&scene=21&sn=5e0514b86258eebed20740dce83a6d45#wechat_redirect