PL SQL regular expression substring

Question

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.

Answer 1

• 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

Answer 2

Here is a general solution - with possibly more than one input string, and with inputs of any length. The only assumption is that no single word may be more than 15 characters, and that everything between two spaces is considered a word. If a "word" can be more than 15 characters, the solution can be adapted, but the requirement itself would need to state what the desired result is in such a case.

I make up two input strings in a CTE (at the top) - that is not part of the solution, it is just for testing and illustration. I also wrote this in plain SQL - there is no need for PL/SQL code for this type of problem. Set processing (instead of one row at a time) should result in much better execution.

The approach is to identify the location of all spaces (I append and prepend a space to each string, too, so I won't have to deal with exceptions for the first and last substring); then I decide, in a recursive subquery, where each "maximal" substring should begin and where it should end; and then outputting the substrings is trivial. I used a recursive query, that should work in Oracle 11.1 (or 11.2 with the syntax I used, with column names in CTE declarations - it can be changed easily to work in 11.1). In Oracle 12, it would be easier to rewrite the same idea using MATCH_RECOGINZE.

with
     inputs ( id, str ) 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
     ),
     positions ( id, pos ) as (
       select id, instr(' ' || str || ' ', ' ', 1, level)
       from   inputs
       connect by level <= length(str) - length(replace(str, ' ')) + 2
              and prior id = id
              and prior sys_guid() is not null
     ),
     r ( id, str, line_number, pos_from, pos_to ) as (
       select id, ' ' || str || ' ', 0, null, 1
         from inputs
       union all
       select r.id, r.str, r.line_number + 1, r.pos_to,
              ( select max(pos)
                from   positions 
                where id = r.id and pos - r.pos_to between 1 and 16
              )
         from r
         where pos_to is not null
     )
select id, line_number, substr(str, pos_from + 1, pos_to - pos_from - 1) as line_text
from   r
where  line_number > 0 and pos_to is not null
order by id, line_number
;

Output:

  ID LINE_NUMBER LINE_TEXT
---- ----------- ---------------
 101           1 I loooove my
 101           2 pet animal
 102           1 1992 was a
 102           2 great year for
 102           3 - make
 102           4 something up
 102           5 here as needed

7 rows selected.