Oracle sql REGEXP to split a string at certain point so as to maintain the meaning- Suggestions required

I have a string for more than 40 characters and wanted to split it into two meaning full strings to set it into two different fields.

Example: SELECT ‘THIS LONG STRING HAS EIGHT WORDS SEVEN SPACES’ FROM DUAL;

Length of the string is 45

I need to split this string into two and set in two different variables of length 40 each however I do-not want to break the string in between.

VAL1 >> SUBSTR of 0,40 looks like : THIS LONG STRING HAS EIGHT WORDS SEVEN S
VAL2 >> SUBSTR of 41,80 looks like : PACES

Instead i want to VAL1 to be >> THIS LONG STRING HAS EIGHT WORDS SEVEN (with or without space at the end is fine)
VAL2 >> SPACES

I tried REGEXP for fetching count of spaces and then splitting each word and then concatenated all as per my requirement. However that is not a good solution .. is there a way I could do it efficiently?

Answer

This could be a way:

with test(x) as
(
    SELECT 'THIS LONG STRING HAS EIGHT WORDS SEVEN SPACES' FROM DUAL
)
select substr(x, 1, 40-instr(reverse(substr(x, 1, 40)), ' ') ) s1,
       substr(x, 40-instr(reverse(substr(x, 1, 40)), ' ')+2) s2 
from test;  

which gives:

S1                                     S2
-------------------------------------- ------
THIS LONG STRING HAS EIGHT WORDS SEVEN SPACES

The idea is to use the reverse to work on the string in reverse order, then look for the last space in the first 40 characters (or the first space in the reverted string) and use this position to split the string.