Fetch a set of words(char and non char seperated by space) from a sentence appearing prior to a particular words in Oracle Sql

I want to split a sentence and fetch a subset of words seperated by space , prior to a specific word in Oracle SqL.

I tried regexp substring, instring but couldn’t arrive at solution.

Source sentences: Being 25 % commission on package item charged

Being 27.5 % Withholding tax on items debited

Being 30.09876 % survey fees on services delivered.

Condition: Fetch all words prior to the first appearance of word ‘on’

Expected output: Being 25 % commission

Being 27.5 % withholding tax

Being 30.09876 % survey fees

Answer

You can use the regular expression ^(.*?[^[:alpha:]])??(on[^[:alpha:]].*)?$ and, if matched, replace the value with the contents of the first capturing group:

SELECT value,
       REGEXP_REPLACE(
         value,
         '^(.*?[^[:alpha:]])??(on[^[:alpha:]].*)?$',
         '1',
         1,
         1,
         'i'
       ) AS value_until_first_on
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT 'Being 25 % commission on package item charged' FROM DUAL UNION ALL
SELECT 'Being 27.5 % Withholding tax on items debited' FROM DUAL UNION ALL
SELECT 'Being 30.09876 % survey fees on services delivered.' FROM DUAL UNION ALL
SELECT 'Alice has a hat with flowers on, it is pretty.' FROM DUAL UNION ALL
SELECT 'On Tuesday, it was hot.' FROM DUAL UNION ALL
SELECT 'This sentence ends with on' FROM DUAL UNION ALL
SELECT 'One Two Three Four' FROM DUAL UNION ALL
SELECT 'Four Three Two One Zero' FROM DUAL UNION ALL
SELECT 'We went, on Tuesday, on the train.' FROM DUAL;

Outputs:

VALUE                                               | VALUE_UNTIL_FIRST_ON         
:-------------------------------------------------- | :----------------------------
Being 25 % commission on package item charged       | Being 25 % commission        
Being 27.5 % Withholding tax on items debited       | Being 27.5 % Withholding tax 
Being 30.09876 % survey fees on services delivered. | Being 30.09876 % survey fees 
Alice has a hat with flowers on, it is pretty.      | Alice has a hat with flowers 
On Tuesday, it was hot.                             | null                         
This sentence ends with on                          | This sentence ends with on   
One Two Three Four                                  | One Two Three Four           
Four Three Two One Zero                             | Four Three Two One Zero      
We went, on Tuesday, on the train.                  | We went,                     

db<>fiddle here