Split comma separated values of a column in row, through Oracle SQL query

by

Try using below query:

 WITH T AS (SELECT 'A,B,C,D,E,F' STR  FROM DUAL)   SELECT    
 REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES  FROM T 
 CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM T)

Below Query with ID:

WITH TAB AS 
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
)
SELECT    ID, 
REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES  FROM TAB 
CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM TAB);

EDIT:
Try using below query for multiple IDs and multiple separation:

WITH TAB AS 
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
UNION
SELECT '1002' ID, 'D,E,F' STR FROM DUAL
UNION
SELECT '1003' ID, 'C,E,G' STR FROM DUAL
)
select id, substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name 
from
( select ',' || STR || ',' as STR, id from TAB ),
( select level as lvl from dual connect by level <= 100 )
where lvl <= length(STR) - length(replace(STR, ',')) - 1

order by ID, NAME

More Related Contents:

  1. Split comma separated values to columns in Oracle
  2. Split String by delimiter position using oracle SQL
  3. oracle — Split multiple comma separated values in oracle table to multiple rows
  4. Oracle- Split string comma delimited (string contains spaces and consecutive commas)
  5. Search All Fields In All Tables For A Specific Value (Oracle)
  6. Oracle: how to UPSERT (update or insert into a table?)
  7. Get top results for each group (in Oracle)
  8. Paging with Oracle
  9. Oracle Differences between NVL and Coalesce
  10. ORA-12560: TNS:protocol adaptor error
  11. What exactly do quotation marks around the table name do?
  12. Calculating difference between two timestamps in Oracle in milliseconds
  13. How can I get column names from a table in Oracle?
  14. How to store only time; not date and time?
  15. How does GROUP BY work?
  16. How can I find which tables reference a given table in Oracle SQL Developer?
  17. Oracle equivalent of Postgres’ DISTINCT ON?
  18. How to get the last row of an Oracle table
  19. What is the difference between ‘YYYY’ and ‘RRRR’ in Oracle SQL
  20. Split one column value into multiple column values
  21. Oracle – How to create a materialized view with FAST REFRESH and JOINS
  22. SQL Oracle LEFT JOIN and SUBQUERY error: ORA-00905: missing keyword
  23. Does ‘Select’ always order by primary key?
  24. How to use a dynamic parameter in a IN clause of a JPA named query?
  25. Search an Oracle database for tables with specific column names?
  26. How to replace specific values in a oracle database column?
  27. ORA-01861: literal does not match format string
  28. Query a Table’s Foreign Key relationships
  29. IN vs OR of Oracle, which faster?
  30. is there a PRODUCT function like there is a SUM function in Oracle SQL?

Leave a Comment