PL SQL regular expression substring

I have a long string.

message := 'I loooove my pet animal';

This string in 23 chars long. If message is greater that 15 chars, I need to find the length of message where I can break the string into 2 strings. For example, in this case,

message1 := 'I loove my'
message2 := 'pet animal'

Essentially it should find the position of a whole word at the previous to 15 chars and the break the original string into 2 at that point.

Please give me ideas how I can do this.

Thank you.

Solution

First you reverse string.
```
SELECT REVERSE(strField) FROM DUAL;
```
Then you calculate length i = length(strField).

Then find the first space after the middle

j := INSTR( REVERSE(strField), ' ', i / 2,  i)`

Finally split by i - j (maybe +/- 1 need to test it)

DEMO

WITH parameter (id, strField) as (
    select 101, 'I loooove my pet animal' from dual union all
    select 102, '1992 was a great year for - make something up here as needed' from dual union all
    select 103, 'You are Supercalifragilisticexpialidocious' from dual  
), prepare (id, rev, len, middle) as (
    SELECT id, reverse(strField), length(strField), length(strField) / 2
    FROM parameter
)    
SELECT p.*, l.*, 
       SUBSTR(strField, 1, len -  INSTR(rev, ' ', middle)) as first, 
       SUBSTR(strField, len -  INSTR(rev, ' ', middle) + 2, len) as second
FROM parameter p
JOIN prepare l
  ON p.id = l.id

OUTPUT