problem in finding pattern in regular expression in clob column

I have a problem with regex in oracle.
I am trying to detect the pattern in the text below
Suspect(s) detected by OFAC-Agent:25 
SystemId: 
Associate: 
=============================
Suspect detected #1

OFAC ID:AS06762733
MATCH: 0.00
TAG: NAM
MATCHINGTEXT: Masked,**,Masked, 
RESULT: (0)

BATCH: 2018/03/24_0001_IN_ONDM_PRI3_2
NAME: Masked,Masked
  Synonyms: none
ADDRESS: Masked
  Synonyms: none
CITY: Masked
  Synonyms: none
COUNTRY: Masked
  Synonyms:
   - REPUBLIC OF Masked
   - Masked
   - Masked
   - Masked
   - Masked
STATE: Masked
  Synonyms: none
ORIGIN: 
EDA
DESIGNATION: 
GWL
TYPE: 
I
SEARCH CODES: 
none
USER DATA 1: 
none
USER DATA 2: 
none
OFFICIAL REF: 
2017-09-05 20:28:59 EDA
PASSPORT: 
none
BIC CODES: 
none
NATID: 
none
PLACE OF BIRTH: 
none
DATE OF BIRTH: 
none
NATIONALITY: 
none
ADDITIONAL INFOS: 
List ID: 1106 / Create Date: 09/05/2017 20:28:59 / Last Update Date: 09/05/2017 20:28:59 / Org_PID: 8388550 / Title: ARRESTED FOR BURGLARY - AUGUST, 2017 / Gender: MALE / OtherInformation: NickName: Masked; According to the timesofindia.indiatimes.com; August 25, 2017: In August, 2017, Masked was arrested for burglary. blah blha  blah blah blah blah. They / Relationship: Co-Defendant / OriginalID: 8388619
FML TYPE: 
1
FML PRIORITY: 
0
FML CONFIDENTIALITY: 
0
FML INFO: 
none
PEP-FEP: 
0 0
KEYWORDS: 
OS:ADVERSE_MEDIA NS:NAMESOURCE_WEBSITE ENTITYLEVEL:LEVEL_NA SC:ORGANIZED_CRIME
HYPERLINKS: 
https://MASKED.MASKED.com/signin.aspx?ent=87272dba-54dc-4246-919c-9aab50619d79
TYS: 1
ISN: 0

=============================
Suspect detected #2

OFAC ID:AS05135512
MATCH: 0.00
TAG: NAM
MATCHINGTEXT: Masked, country 
RESULT: (0)

BATCH: 2018/02/22_0001_XX_XXXY_ABC_1
NAME: Masked, Masked
  Synonyms: none
ADDRESS: Masked Masked
  Synonyms: none
CITY: MASKED
  Synonyms: none
COUNTRY: MASKED
  Synonyms:
   - REPUBLIC OF MASKED
   - MASKEd
   - MASKEd
   - MASKED MASKED
   - MASKED
STATE: MASKED
  Synonyms: none
ORIGIN: 
EDA
DESIGNATION: 
GWL
TYPE: 
I
SEARCH CODES: 
none
USER DATA 1: 
none
USER DATA 2: 
none
OFFICIAL REF: 
2017-11-06 15:03:12 EDA
PASSPORT: 
none
BIC CODES: 
none
NATID: 
none
PLACE OF BIRTH: 
none
DATE OF BIRTH: 
none
NATIONALITY: 
none
ADDITIONAL INFOS: 
List ID: 1106 / Create Date: 10/16/2015 00:00:00 / Last Update Date: 11/06/2017 15:03:12 / Org_PID: 789137 / Title: SENTENCED TO PAY A FINE OF INR 2,000 FOR VIOLATION OF CIS REGULATIONS - SEPTEMBER 25, 2006. / Gender: MALE / OtherInformation: According to the Masked-Securities and Exchange Board of Masked; March 31, 2014: Court(Filed/ Declared PO): ACMM Tis Hazari Court Delhi / ASJ, Delhi Name of Case: M/s. Masked Ltd. and Others Name of Accused: 1.Mr. Masked Ltd. 2. / Relationship: Associate / OriginalID: 7116687
FML TYPE: 
1
FML PRIORITY: 
0
FML CONFIDENTIALITY: 
0
FML INFO: 
none
PEP-FEP: 
0 0
KEYWORDS: 
OS:ENFORCEMENT NS:NAMESOURCE_IN-SEBI ENTITYLEVEL:LEVEL_NA SC:DISCIPLINED
HYPERLINKS: 
https://masked.MASKED.com/signin.aspx?ent=e1e528ad-658e-4ce9-abd9-6a6315331576
TYS: 1
ISN: 0
=============================

Open in new window


 the above data is stored in clob column named details in a table ABC
I am using the below regex

select regexp_substr(details,'[^=]+',1,rownum+1) from ABC

Output:

First Row

Suspect detected #1

OFAC ID:AS06762733
MATCH: 0.00
TAG: NAM
MATCHINGTEXT: Masked,**,Masked,
RESULT: (0)

BATCH: 2018/03/24_0001_IN_ONDM_PRI3_2
NAME: Masked,Masked
  Synonyms: none
ADDRESS: Masked
  Synonyms: none
CITY: Masked
  Synonyms: none
COUNTRY: Masked
  Synonyms:
   - REPUBLIC OF Masked
   - Masked
   - Masked
   - Masked
   - Masked
STATE: Masked
  Synonyms: none
ORIGIN:
EDA
DESIGNATION:
GWL
TYPE:
I
SEARCH CODES:
none
USER DATA 1:
none
USER DATA 2:
none
OFFICIAL REF:
2017-09-05 20:28:59 EDA
PASSPORT:
none
BIC CODES:
none
NATID:
none
PLACE OF BIRTH:
none
DATE OF BIRTH:
none
NATIONALITY:
none
ADDITIONAL INFOS:
List ID: 1106 / Create Date: 09/05/2017 20:28:59 / Last Update Date: 09/05/2017 20:28:59 / Org_PID: 8388550 / Title: ARRESTED FOR BURGLARY - AUGUST, 2017 / Gender: MALE / OtherInformation: NickName: Masked; According to the timesofindia.indiatimes.com; August 25, 2017: In August, 2017, Masked was arrested for burglary. blah blha  blah blah blah blah. They / Relationship: Co-Defendant / OriginalID: 8388619
FML TYPE:
1
FML PRIORITY:
0
FML CONFIDENTIALITY:
0
FML INFO:
none
PEP-FEP:
0 0
KEYWORDS:
OS:ADVERSE_MEDIA NS:NAMESOURCE_WEBSITE ENTITYLEVEL:LEVEL_NA SC:ORGANIZED_CRIME
HYPERLINKS:
https://MASKED.MASKED.com/signin.aspx?ent

Second Row
87272dba-54dc-4246-919c-9aab50619d79
TYS: 1
ISN: 0

Where in i expect second row to be starting from Suspect Detected #2 like the first row,
pls help and let me know if question is not clear

I am trying to use it in joining with the other table where there is a co-relation of data with the output of this query, hence the rownum condition is there...

I want to find all strings between(=============================) there are exactly 29 = signs( but somehow) couldn't do this, tried to search but no expected results
LVL 16
Wasim Akram ShaikAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try something like this
select regexp_substr(details,'(\={14}'||CHR(10)||')(.*?)('||CHR(10)||'\={14})',1,rownum+1,'n',2) as f from ABC

Open in new window

REgards
0
 
Wasim Akram ShaikAuthor Commented:
Thanks Rgonzo, it worked perfectly
0
 
Wasim Akram ShaikAuthor Commented:
really appreciate this.. excellent.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Wasim Akram ShaikAuthor Commented:
if you don't mind.. could you please explain me.. why you have counted for only {14} occurences rather than 28..
0
 
Rgonzo1971Commented:
since I cannot use non capturing Groups If I used 29 I couldn't find the second part
1
 
Wasim Akram ShaikAuthor Commented:
understood, thanx for clarification
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.