oracle wm_concat实现字符串分割替换

发表于 | 更新于 | 分类于 ， | 评论数： | 阅读次数：

背景

在数据库中有一个存储有权限用户的字段，通过,分割来存储了多个用户的账号。可以通过用户账号在用户表中查询出用户的姓名，现在需要将这个字段的账号转换成用户姓名在前台展示。

  • 用户表user：
idname
0001小明
0002小刚
  • 需要处理的字段：0001,0002。
  • 要求的返回结果：小明,小刚。

思路

  • 首先我们需要把user表中id字段存在在处理字段中的值给查出来。

    最开始考虑使用instr函数：

    1
    select * from user where instr('0001,0002', id)>0

    instr()函数可以判断值在字符串中的位置，如果大于0，也就是存在。但是这是模糊匹配的，会有问题。比如上面场景中，如果有用户id是000那么也会匹配出来。

    所以使用regexp_like()，通过正则来判断。

    这里的正则表达如为：^(0001|0002)$。使用^确定开通，$确认结尾，|用来分割允许的值。对应到实际的就变成如下：

    1
    select name from user where regexp_like(id, '^(' || replace('0001,0002', ',', '|') || ')$')

    注意：很多正则判断工具里^0001|0002$也可以实现效果，但oracle中，必须加上()，否则oracle会认为是^0001 和0002$，0001001这样的也会被识别到。

    输出结果：

name
小明
小刚

  • 然后可以使用wm_concat函数把连起来即可。

    wm_concat是一个未被记录的函数，但是可以实现将列通过,连成一个字段的作用。

    1
    select to_char(wmsys.wm_concat(name)) from user where id = '0001' or id = '0002'

    https://community.oracle.com/thread/1090158

结论

连起来结果如下：

1
select to_char(wmsys.wm_concat(name)) name from user where regexp_like(id, '^'|| replace('0001,0002', ',', '|')||'$')

输出结果：

name
小明,小刚

注意

由于wm_concat未被官方记录，不同版本的oracle有区别，如果输出是个clob类型，那么可以使用to_char()来转换。也可以考虑使用listagg()来实现。