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 'fulfilled' FROM DUAL UNION ALL
SELECT 'fulfillment' FROM DUAL;
8 rows affected

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


WORD
SUBSTRING
ISLEAF


dog
null
1

games
null
1

stat
null
0

state
stat
0

statement
stat
1

state
null
0

statement
state
1

statement
null
1

fulfill
null
0

fulfilled
fulfill
1

fulfillment
fulfill
1

fulfilled
null
1

fulfillment
null
1


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

fulfilled
fulfill

fulfillment
fulfill

games
null

dog
null

WORD	SUBSTRING	ISLEAF
dog	null	1
games	null	1
stat	null	0
state	stat	0
statement	stat	1
state	null	0
statement	state	1
statement	null	1
fulfill	null	0
fulfilled	fulfill	1
fulfillment	fulfill	1
fulfilled	null	1
fulfillment	null	1