PL/SQL Split string by pattern

Question

Similarly to this question...

How can I use regex to split a string, using a string as a delimiter?

... I'm trying to split the following string:

Spent 30 CAD in movie tickets at Cineplex on 2018-06-01

My desired output is this:

ELEMENT ELEMENT_VALUE
------- -------------
      1 Spent
      2 30
      3 CAD
      4 movie tickets
      5 Cineplex
      6 2018-06-01

Similarly, it should be able to process:

Paid 600 EUR to Electric Company

Producing:

ELEMENT ELEMENT_VALUE
------- -------------
      1 Paid
      2 600
      3 EUR
      4 
      5 Electric Company

I've tried this regular expression to no avail:

(\w+)(\D+)(\w+)(?(?=in)(\w+)(at)(\w+)(on)(.?$)|((?=to)(\w+)(.?$)))

I've looked on a couple of regular expression websites plus this post without much luck:

Extract some part of text separated by delimiter using regex

Could someone please help?

Answer 1

Here's a simple SQL tokenizer that breaks on a space:

select regexp_substr('Spent 30 CAD in movie tickets at Cineplex on 2018-06-01','[^ ]+', 1, level) from dual
connect by regexp_substr('Spent 30 CAD in movie tickets at Cineplex on 2018-06-01', '[^ ]+', 1, level) is not null

From: https://blogs.oracle.com/aramamoo/how-to-split-comma-separated-string-and-pass-to-in-clause-of-select-statement