Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE words (word) AS
SELECT 'dog' FROM DUAL UNION ALL
SELECT 'games' FROM DUAL UNION ALL
SELECT 'stat' FROM DUAL UNION ALL
SELECT 'state' FROM DUAL UNION ALL
SELECT 'statement' FROM DUAL UNION ALL
SELECT 'fulfill' FROM DUAL UNION ALL
SELECT 'fulfills' FROM DUAL UNION ALL
SELECT 'fulfilled' FROM DUAL UNION ALL
SELECT 'fulfillment' FROM DUAL;
9 rows affected

SELECT word,
       substring
FROM   words
MATCH_RECOGNIZE (
  ORDER BY word DESC
  MEASURES
    PREV(word.word) AS prev_word,
    word.word AS word,
    substring.word AS substring
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO NEXT ROW
  PATTERN (word (non_substring*? substring)?)
  DEFINE
    substring     AS word.word LIKE word || '_%'
)
WHERE  prev_word IS NULL
OR     prev_word NOT LIKE word || '%'


WORD
SUBSTRING


statement
state

games
null

fulfills
fulfill

fulfillment
fulfill

fulfilled
fulfill

dog
null


SELECT word,
       MAX(PRIOR word) AS substring
FROM   words
WHERE  CONNECT_BY_ISLEAF = 1
AND    LEVEL IN (1,2)
CONNECT BY word LIKE PRIOR word || '%'
AND        PRIOR ROWID != ROWID
GROUP BY word;


WORD
SUBSTRING


statement
state

fulfills
fulfill

fulfilled
fulfill

fulfillment
fulfill

games
null

dog
null

WORD	SUBSTRING
statement	state
games	null
fulfills	fulfill
fulfillment	fulfill
fulfilled	fulfill
dog	null