Split and compare two Strings in Oracle SQL

I have a table with three columns structured as followed:

+------------------------+------------------------------+--------------+
| left                   | right                        | pattern      |
+------------------------+------------------------------+--------------+
| Kiki Cola 50 ml bottle | Kiki Cola 50 ml bottle       |              |
+------------------------+------------------------------+--------------+
| Kiki Cola 50 ml bottle | 50 ml Kiki Cola bottle       |              |
+------------------------+------------------------------+--------------+
| Kiki Cola 50 ml bottle | Kiki Cola 50 ml              |              |
+------------------------+------------------------------+--------------+
| Kiki Cola 50 ml bottle | Kiki Cola Light bottle 50 ml |              |
+------------------------+------------------------------+--------------+
| Kiki Cola 50 ml bottle | Coca Cola 50 ml bottle       |              |
+------------------------+------------------------------+--------------+

Now I would like to perform an Oracle-SQL-query which gives me the edit-pattern of the two strings left and right. The result should be as followed:

+------------------------+------------------------------+--------------+
| left                   | right                        | pattern      |
+------------------------+------------------------------+--------------+
| Kiki Cola 50 ml bottle | Kiki Cola 50 ml bottle       | SAME         |
+------------------------+------------------------------+--------------+
| Kiki Cola 50 ml bottle | 50 ml Kiki Cola bottle       | SWAPPED      |
+------------------------+------------------------------+--------------+
| Kiki Cola 50 ml bottle | Kiki Cola 50 ml              | CONTAINED_IN |
+------------------------+------------------------------+--------------+
| Kiki Cola 50 ml bottle | Kiki Cola Light bottle 50 ml | CONTAINS     |
+------------------------+------------------------------+--------------+
| Kiki Cola 50 ml bottle | Coca Cola 50 ml bottle       | NOT_SAME     |
+------------------------+------------------------------+--------------+ 

Al my tries with REGEX_SPLIT and CONNECT BY were not successful. Do you have any ideas how to solve that problem?

Answer

You can create a collection data type:

CREATE TYPE stringlist IS TABLE OF VARCHAR2(200);

And then split the strings into collections of words and compare the collections:

SELECT left,
       right,
       CASE
       WHEN left = right THEN 'same'
       WHEN left_words = right_words THEN 'swapped'
       WHEN left_words SUBMULTISET OF right_words THEN 'contains'
       WHEN right_words SUBMULTISET OF left_words THEN 'contained in'
       ELSE 'not_same'
       END AS pattern
FROM   (
  SELECT left,
         right,
         ( SELECT CAST(
                    COLLECT( REGEXP_SUBSTR( left, '[^ ]+', 1, LEVEL ) )
                    AS stringlist
                  )
           FROM   DUAL
           CONNECT BY
                  LEVEL <= REGEXP_COUNT( left, '[^ ]+' )
         ) AS left_words,
         ( SELECT CAST(
                    COLLECT( REGEXP_SUBSTR( right, '[^ ]+', 1, LEVEL ) )
                    AS stringlist
                  )
           FROM   DUAL
           CONNECT BY
                  LEVEL <= REGEXP_COUNT( right, '[^ ]+' )
         ) AS right_words
  FROM   test_data t
)

So for your test data:

CREATE TABLE test_data ( left, right ) AS
SELECT 'Kiki Cola 50 ml bottle', 'Kiki Cola 50 ml bottle' FROM DUAL UNION ALL
SELECT 'Kiki Cola 50 ml bottle', '50 ml Kiki Cola bottle' FROM DUAL UNION ALL
SELECT 'Kiki Cola 50 ml bottle', 'Kiki Cola 50 ml' FROM DUAL UNION ALL
SELECT 'Kiki Cola 50 ml bottle', 'Kiki Cola Light 50 ml bottle' FROM DUAL UNION ALL
SELECT 'Kiki Cola 50 ml bottle', 'Coca Cola 50 ml bottle' FROM DUAL;

The query outputs:

LEFT                   | RIGHT                        | PATTERN     
:--------------------- | :--------------------------- | :-----------
Kiki Cola 50 ml bottle | Kiki Cola 50 ml bottle       | same        
Kiki Cola 50 ml bottle | 50 ml Kiki Cola bottle       | swapped     
Kiki Cola 50 ml bottle | Kiki Cola 50 ml              | contained in
Kiki Cola 50 ml bottle | Kiki Cola Light 50 ml bottle | contains    
Kiki Cola 50 ml bottle | Coca Cola 50 ml bottle       | not_same    

db<>fiddle here