[^(\|\|)]* matches everything after "||Private Notes" that is not "||".

That is incorrect. That regular expression matches every character that is not a left parenthesis, a backslash, a pipe character or a right parenthesis. There are no escape sequences in a character class (either a standard one or a negated one); and character classes (including negated character classes) can only match one character at a time, there is no grouping in character classes.

Some regexp dialects (but not the one chosen by Oracle) support escape sequences in character classes. No dialect supports grouping of multiple characters within a character class, negated or otherwise.

Before suggesting another solution to the OP, one would need to understand what he meant by the || sequence. He is talking about "lines" in the title; perhaps he showed us || as a placeholder for "newline", where the CLOB has actual newline characters, chr(10)? Or are those double-pipes really hard-coded in the CLOB, as a line separator?

The updated answer is better, but still not perfect. There is still the possibility that some text that must be removed will contain a single pipe (not two consecutive pipes), and such characters will stop the deletion of text. For example:

||Private Notes This note contains |, a pipe character

Deletion will stop at the single pipe character in the middle of the sentence; the pipe, the comma, the space, and the rest of the text will not be deleted.

One might wonder why single-pipes would be present in text; but if they aren't, one might equally well wonder why TWO pipes indicate the end of a line, instead of a single one.

It is possible to write a 100% correct solution, that will not trip over single-pipes. Another thing that must be fixed - what if the very first "line" must be removed? It begins with the start-of-string anchor, rather than with two pipes. (Easy to fix.) I would still wait for clarification from the OP though, whether he really did mean that those two-consecutive-pipes fragments exist as written in the CLOB.

Hi Tom,

Thanks for the answer that really helps. But just wanted to understand

Example text:
Customer Input (25/04/22): What is your name? Problem Summary: Identify text. +++ Customer Input: My name is XYZ.

1. I want to identify, how many times customer input is present in the text
2.Any customer input before problem summary needs to be excluded.
Expected output:

Text | Counter(No of times customer input present)
--------------------------------------------------------------------
Customer Input: My name is XYZ, 2

with rws as (
  select 'Customer Input (25/04/22): What is your name? Problem Summary: Identify text. +++ Customer Input: My name is XYZ.' str 
  from   dual
)
  select regexp_count ( str, 'Customer Input' ) num,
         regexp_replace ( str, '(.*)(Customer Input)', '\2' ) last
  from   rws;
  
       NUM LAST                           
---------- -------------------------------
         2 Customer Input: My name is XYZ.