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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s