oracle-将oracle表中的多个逗号分隔的值拆分为多行 [英] oracle -- Split multiple comma separated values in oracle table to multiple rows

问题描述

我在使用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

编辑

上面的select查询只能分割以逗号分隔的字符串，但是，在具有多行的表上执行时，它会产生重复的行.如何限制重复的行?

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

这篇关于oracle-将oracle表中的多个逗号分隔的值拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！