substring and finding string and adding

Question

I am analysing columns which have string in it as en example

"
Reading short stories in English is a
REELED IN £80.00 OF WINS
Do you enjoy reading stories?

Reading short stories in English is a great way to improve your language level. 
In this section, read our short stories dat were specially written 
REELED IN £180.00 OF WINS

for English language learners. There are 
two sections, one for lower level learners (A2/B1)
 and one for higher levels (B2/C1). 
REELED IN £60.00 OF WINS

You will improve you're reading fluency and 
comprehension and develop you're vocabulary. 
Each story TEMPhas interactive exercises to help you understand and use the language."

I need to find how many times Reeled are available in the string, and this can be random. May be 3,4,5 anything. And with every reeled there is an amount mentioned with each reeled. I need a column which can add all this amount together. In this case,

80+180+60=320

If I know how many reeled is there every time, then I can use substr and instr and add the values. Not sure how to do if reeled are there random times. Please suggest...

Answer 1

At the end, that's some kind of a hierarchical query.

For sample data you posted:

SQL> select * From temp;

COL
--------------------------------------------------
Reading short stories in English is a
REELED IN L80.00 OF WINS
Do you enjoy reading stories?
Reading short stories in English is a great way to
 improve your language level.
In this section, read our short stories dat were s
pecially written
REELED IN L180.00 OF WINS

for English language learners. There are
two sections, one for lower level learners (A2/B1)

 and one for higher levels (B2/C1).
REELED IN L60.00 OF WINS

You will improve you're reading fluency and
comprehension and develop you're vocabulary.
Each story TEMPhas interactive exercises to help y
ou understand and use the language.

Setting NLS_NUMERIC_CHARACTERS (as your decimal separator is a dot; in my Croatian database, it is a comma); you probably don't have to do that:

SQL> alter session set nls_numeric_characters = '.,';

Session altered.

Query uses substr + instr combination (as you already said) which extracts the numeric part between REELED IN and OF WINS, but with addition of the connect by clause which "loops" through the string as many times as there's the REELED IN substring in it.

SQL> select
  2    sum(substr(col, instr(col, 'REELED IN', 1, level) + 11,
  3                    instr(col, 'OF WINS', 1, level) - instr(col, 'REELED IN', 1, level) - 11
  4              )) res
  5  from temp
  6  connect by level <= regexp_count(col, 'REELED IN');

       RES
----------
       320

SQL>