Oracle :: String Tokenizer with Oracle

Scenario is to break a string separated by pipe “|” in between. In the below screen shot column C is getting split based on pipe “|”

Desired output as follows. second column has split into separate rows.

Query used

SELECT regexp_substr(str, ‘[^|]+’, 1, LEVEL) AS splitted_element,
LEVEL AS element_no
FROM (SELECT rownum AS id, str FROM TEST_TABLE
WHERE ID=’15607′)
CONNECT BY instr(str, ‘|’, 1, LEVEL – 1) > 0
AND id = PRIOR id
AND PRIOR dbms_random.value IS NOT null;

Advertisements