Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT distinct keyvalue, level pos, trim(regexp_substr(text, 'Customer Input[^+++]*', 1, level))  x
  FROM 
  (
    SELECT 101 as keyvalue,'Customer Input 05/15/2023 07:20:20 My name is ABX +++ Private Notes What is you name+++Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 
    +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English| '
    as text from dual
    union all 
    SELECT 102 as keyvalue,' Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket+++Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one 
    +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English| '
    as text from dual  

  ) t
CONNECT BY instr(text, 'Customer Input', 1, level - 1) > 0
order by keyvalue;


KEYVALUE
POS
X


101
1
Customer Input 05/15/2023 07:20:20 My name is ABX

101
2
Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 


101
3
Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English|

101
4
null

102
1
Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket

102
2
Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one 


102
3
Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English|

102
4
null


SELECT distinct keyvalue, level pos, trim(regexp_substr(text, 'Customer Input[^+++]*', 1, level))  x
  FROM 
  (
    SELECT 101 as keyvalue,to_clob('Customer Input 05/15/2023 07:20:20 My name is ABX +++ Private Notes What is you name+++Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 
    +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English| ')
    as text from dual
    union all 
    SELECT 102 as keyvalue,to_clob(' Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket+++Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one 
    +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English| ')
    as text from dual  

  ) t
CONNECT BY instr(text, 'Customer Input', 1, level - 1) > 0
order by keyvalue;
ORA-00932: inconsistent datatypes: expected - got CLOB

SELECT keyvalue, level pos, trim(regexp_substr(text, 'Customer Input[^+++]*', 1, level))  x
  FROM 
  (
    SELECT 101 as keyvalue,to_clob('Customer Input 05/15/2023 07:20:20 My name is ABX +++ Private Notes What is you name+++Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 
    +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English| ')
    as text from dual
    union all 
    SELECT 102 as keyvalue,to_clob(' Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket+++Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one 
    +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English| ')
    as text from dual  

  ) t
CONNECT BY instr(text, 'Customer Input', 1, level - 1) > 0
order by keyvalue;


KEYVALUE
POS
X


101
1
Customer Input 05/15/2023 07:20:20 My name is ABX

101
4


101
4


101
3
Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English|

101
4


101
4


101
3
Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English|

101
2
Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 


101
4


101
4


101
3
Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English|

101
4


101
2
Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 


101
3
Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English|

101
4


102
4


102
3
Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English|

102
4


102
2
Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one 


102
4


102
3
Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English|

102
4


102
1
Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket

102
4


102
3
Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English|

102
4


102
2
Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one 


102
4


102
3
Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English|

102
4



SELECT keyvalue, level pos, trim(regexp_substr(text, 'Customer Input[^+++]*', 1, level))  x
  FROM 
  (
    SELECT 101 as keyvalue,to_clob('Customer Input 05/15/2023 07:20:20 My name is ABX +++ Private Notes What is you name+++Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 
    +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English| ')
    as text from dual
    union all 
    SELECT 102 as keyvalue,to_clob(' Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket+++Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one 
    +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English| ')
    as text from dual  

  ) t
CONNECT BY instr(text, 'Customer Input', 1, level - 1) > 0
AND keyvalue = PRIOR keyvalue
AND PRIOR dbms_random.value IS NOT NULL
order by keyvalue;


KEYVALUE
POS
X


101
1
Customer Input 05/15/2023 07:20:20 My name is ABX

101
2
Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 


101
3
Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English|

101
4


102
1
Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket

102
2
Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one 


102
3
Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English|

102
4



-- anchor branch only just to showing starting point
WITH r (keyvalue, text, pos, x) as (
  SELECT keyvalue, text, 1, trim(regexp_substr(text, 'Customer Input[^+++]*', 1, 1))
  FROM 
  (
    SELECT 101 as keyvalue,to_clob('Customer Input 05/15/2023 07:20:20 My name is ABX +++ Private Notes What is you name+++Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 
    +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English| ')
    as text from dual
    union all 
    SELECT 102 as keyvalue,to_clob(' Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket+++Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one 
    +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English| ')
    as text from dual  
  ) t
)
SELECT keyvalue, pos, x
FROM r
order by keyvalue, pos;


KEYVALUE
POS
X


101
1
Customer Input 05/15/2023 07:20:20 My name is ABX

102
1
Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket


WITH r (keyvalue, text, pos, x) as (
  SELECT keyvalue, text, 1, trim(regexp_substr(text, 'Customer Input[^+++]*', 1, 1))
  FROM 
  (
    SELECT 101 as keyvalue,to_clob('Customer Input 05/15/2023 07:20:20 My name is ABX +++ Private Notes What is you name+++Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 
    +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English| ')
    as text from dual
    union all 
    SELECT 102 as keyvalue,to_clob(' Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket+++Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one 
    +++ Public Notes 04/30/2023 18:19:18 +++Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English| ')
    as text from dual  
  ) t
  UNION ALL
  SELECT keyvalue, text, pos + 1, trim(regexp_substr(text, 'Customer Input[^+++]*', 1, pos + 1))
  FROM r
  WHERE instr(text, 'Customer Input', 1, pos) > 0
)
SELECT keyvalue, pos, x
FROM r
order by keyvalue, pos;


KEYVALUE
POS
X


101
1
Customer Input 05/15/2023 07:20:20 My name is ABX

101
2
Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23 


101
3
Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English|

101
4


102
1
Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket

102
2
Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one 


102
3
Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes 
    or maintenance performed? - Language Preference: English|

102
4
KEYVALUE	POS	X
101	1	Customer Input 05/15/2023 07:20:20 My name is ABX
101	2	Customer Input 04/30/2023 19:40:58 I have issue related to water purifier purchased on Jan 23
101	3	Customer Input 04/30/2023 Requesting to send a technicial, we could not bring them up due to the same issue that was looked into in ticket 20092. We dont know if this is the same issue as the previous ticket, but need to know the reason Language Preference: English\|
101	4	null
102	1	Customer Input 05/15/2023 07:20:20 20424596 Reference to the above ticket
102	2	Customer Input 04/30/2023 19:40:58 Plesae replace the item as this is a faulty one
102	3	Customer Input 04/30/2023 17:54:54 Shared the faulty machine pics for quick action Problem Context: When was the issue first observed? - 4/30, 1AM or so Were there any recent changes or maintenance performed? - Language Preference: English\|
102	4	null