SQL Query to Convert Comma separated String values to Rows

By | February 9, 2016
/***********************************************************************
 *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;
WOW! Did you like this post? We'll send more interesting posts like SQL Query to Convert Comma separated String values to Rows to you!
Enter your Email Address: