SQL Query to Convert Comma separated String values to Rows

Shailender Thallam

9 years ago

/***********************************************************************
 *PURPOSE: SQL Query to Convert Comma separated String values to Rows  *
 *AUTHOR: Shailender Thallam                                           *
 ***********************************************************************/
WITH DATA AS
  ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  )
SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
FROM DATA
  CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0;
-------------------
--Real time Example
-------------------
WITH DATA AS
  (SELECT argument_text str
  FROM fnd_concurrent_requests
  WHERE request_id = 1040567
  )
SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
FROM DATA
  CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0;

For Further Reading