oracle -- 将oracle表中的多个逗号分隔值拆分为多行

2021-12-05 00:00:00 regex split sql oracle

我在使用 oracle 拆分查询时遇到问题.

I Have a problem with oracle split query.

在 oracle 查询中使用 connect by 和正则表达式将逗号分隔的数据拆分为多行时，我得到了更多重复行.例如，实际上我的表有 150 行，其中两行有逗号分隔的字符串，所以总的来说我只需要得到 155 行，但我得到了 2000 行.如果我使用 distinct 其工作正常，但我不希望查询结果中有重复的行.

While splitting comma separated data into multiple rows using connect by and regular expression in oracle query I am getting more duplicate rows. for example actually my table having 150 rows in that one two rows having comma separated strings so overall i have to get only 155 rows but i am getting 2000 rows. If i use distinct its working fine but i dont want duplicate rows in query result.

我尝试了以下查询，但是它在查询结果中生成了重复的行:

I tried the following query however it's generating duplicate rows in query result:

WITH CTE AS (SELECT 'a,b,c,d,e' temp,1 slno  FROM DUAL
              UNION 
              SELECT 'f,g',2 from dual
              UNION 
               SELECT 'h',3 FROM DUAL)

SELECT TRIM(REGEXP_SUBSTR( TEMP, '[^,]+', 1, LEVEL)) ,SLNO FROM CTE 
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(temp, '[^,]+')) + 1

编辑

上述选择查询只能拆分单个逗号分隔的字符串，但是在具有多行的表上执行时会产生重复的行.如何限制重复行?

The above select query is only able to split a single comma delimited string, however, it produces duplicate rows when executed on a table with multiple rows. How to restrict the duplicate rows?

推荐答案

最后我想出了这个答案

WITH CTE AS (SELECT 'a,b,c,d,e' temp, 1 slno FROM DUAL
              UNION
              SELECT 'f,g' temp, 2 slno FROM DUAL
              UNION
              SELECT 'h' temp, 3 slno FROM DUAL)
SELECT TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)), slno
FROM CTE
CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+')
    AND PRIOR slno = slno
    AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

相关文章