I have a string which contains a Word SPLIT_HERE at multiple places. I want to split this string into multiple rows by SPLIT_HERE. I can only find a way to split using comma or semicolon or a single character delimiter but cannot figure out a way to split by a word. Is there a way to do so ?

Example String:

The Oracle/PLSQL REPLACE SPLIT_HERE function replaces a sequence SPLIT_HERE of characters.

Desired Output:

The Oracle/PLSQL REPLACE 
function replaces a sequence 
of characters.

SELECT REGEXP_SUBSTR('The Oracle/PLSQL REPLACE SPLIT_HERE function replaces a sequence SPLIT_HERE of characters.', 'SPLIT_HERE', 1, LEVELS.COLUMN_VALUE) A
FROM dual 
CROSS JOIN TABLE(CAST(MULTISET(SELECT LEVEL FROM dual CONNECT BY LEVEL <= 2)AS sys.OdciNumberList)) levels

CodePudding user response：

You can use simple (fast) string functions (instead of slow regular expressions) inside a recursive query:

WITH bounds (value, spos, epos) AS (
  SELECT value,
         1,
         INSTR(value, 'SPLIT_HERE', 1)
  FROM   table_name
UNION ALL
  SELECT value,
         epos   10,
         INSTR(value, 'SPLIT_HERE', epos   10)
  FROM   bounds
  WHERE  epos > 0
)
SELECT CASE epos
       WHEN 0
       THEN SUBSTR(value, spos)
       ELSE SUBSTR(value, spos, epos - spos)
       END AS match
FROM   bounds;

Which for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'The Oracle/PLSQL REPLACE SPLIT_HERE function replaces a sequence SPLIT_HERE of characters.' FROM DUAL;

Outputs:

MATCH
The Oracle/PLSQL REPLACE
function replaces a sequence
of characters.

db<>fiddle here

CodePudding user response：

You can use REGEXP_SUBSTR:

SELECT LEVEL AS element
       , REGEXP_SUBSTR(te_xt,'(.*?)( SPLIT_HERE |$)', 1, LEVEL, NULL, 1 ) AS el_val
FROM test
CONNECT BY LEVEL <= regexp_count(te_xt, ' SPLIT_HERE ') 1;

Here is a demo

CodePudding user response：

You can use the replace function since you want to replace a literal string. No need for regexp_replace.

select replace('The Oracle/PLSQL REPLACE SPLIT_HERE function replaces a sequence SPLIT_HERE of characters.',
               'SPLIT_HERE',
               chr(10)) as RESULT
  from DUAL

Result is:

RESULT
-----------------------------
The Oracle/PLSQL REPLACE
 function replaces a sequence
 of characters.

Refer to this SO question: New Line while inserting into VARCHAR2 column