Oracle数据库，高效率拆分逗号拼接字符串

最新推荐文章于 2024-07-30 19:26:47 发布

Joachim丶

最新推荐文章于 2024-07-30 19:26:47 发布

阅读量994

点赞数 1

文章标签：数据库 oracle sql java Powered by 金山文档

本文链接：https://blog.csdn.net/weixin_46636220/article/details/129665978

版权

列：id: 1 name: a,b,c,d

希望获得：1:a，1:b，1:c，1:d

普遍案列：

select regexp_substr(q.nums, '[^,]+', 1, rownum,'i'), names
from (select '1,2,3' nums, '张三' names from dual) q
connect by rownum <= LENGTH(q.nums) - LENGTH(REGEXP_REPLACE(q.nums, ',', '')) + 1;

效率太差，表中数据一多，查询特别慢

高效率方案

SELECT
    id,
    substr(
        column_name,
        instr( column_name, ',', 1, levels.lvl ) + 1,
        instr( column_name, ',', 1, levels.lvl + 1 ) - ( instr( column_name, ',', 1, levels.lvl ) + 1 ) 
    ) AS column_name 
FROM
    (
    SELECT
        id,
        ',' || column_name || ',' AS column_name,
        length( column_name ) - nvl( length( REPLACE ( column_name, ',' ) ), 0 ) + 1 AS cnt 
    FROM
        table_name 
    ) a,
    (
    SELECT ROWNUM AS
        lvl 
    FROM
        (
        SELECT
            MAX( length( column_name || ',' ) - nvl( length( REPLACE ( column_name, ',' ) ), 0 ) ) max_len 
        FROM
            table_name 
        ) CONNECT BY LEVEL <= max_len 
    ) levels 
WHERE
    levels.lvl <= a.cnt 
ORDER BY
    id