CODEWITHSUNDEEP
oracle-databasesubstring
Praveen Verma
Praveen Verma

Reputation: 41

Oracle SQL: Getting text between two sentences

I am trying to extract a set of strings from within a string which is stored in a table column named 'myColumn', one row of that looks like this-

Type:MOBILE
Service:GSM
Pd to LOOK:
NOKIA/HMD;
APPLE;
MI;

Pd to SEE (swap):
SAMSUNG;
VIVO;

Pd to SEE (extra):
MOKA;

Contact me: 123456

The code I have tried for getting one column is -

SELECT regexp_substr(myColumn, '\"Products to be CEASED:"([^"Products to be PROVIDED (swap):"]+)\}', 1,1,NULL,1) AS output FROM mytable

I need a query that would return text in between the sentences -

'Pd to LOOK:' and 'Pd to SEE (swap):'

'Pd to SEE (swap):' and 'Pd to SEE (extra):'

'Pd to SEE (extra):' and 'Contact me: 123456'

For Example -

Pd to LOOK:            Pd to SEE (swap):  Pd to SEE (extra):
NOKIA/HMD; APPLE; MI;  SAMSUNG;VIVO;      MOKA;

Upvotes: 0

Views: 176

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

A straight solution is to use substr function in conjunction with instr function. Or DBMS_LOB.instr and substr if the datatype of the column is a LOB.

documentation for dbms_lob.instr

for example:

select 
     substr(
       mycolumn,
       instr(mycolumn,'Pd to LOOK:'), --starting position
       instr(mycolumn,'Pd to SEE (swap):') - instr(mycolumn,'Pd to LOOK:'), --amount to get
       ) as "Pd to LOOK:"
     --, other expressions/columns
from 
     my_table;

Upvotes: 2

Related Questions