Oracle 11g Release 2

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

CREATE TABLE data ( value ) AS
SELECT 'long words like floxinoxinihilipilification and antidisestablishmentarianism with your code 001/XYZ/0002/65432178' FROM DUAL UNION ALL
SELECT 'i would ?! like / to make * bb a8 001/XYZ/0002/65432178 thank you very much friends' FROM DUAL;
2 rows affected

SELECT REGEXP_SUBSTR( value, '\S{20,}' ) AS code,
       value
FROM   data d;


CODE
VALUE


floxinoxinihilipilification
long words like floxinoxinihilipilification and antidisestablishmentarianism with your code 001/XYZ/0002/65432178

001/XYZ/0002/65432178
i would ?! like / to make * bb a8 001/XYZ/0002/65432178 thank you very much friends


SELECT ( SELECT MAX( REGEXP_SUBSTR( d.value, '\S{20,}', 1, LEVEL ) ) KEEP ( DENSE_RANK LAST ORDER BY REGEXP_COUNT( REGEXP_SUBSTR( d.value, '\S{20,}', 1, LEVEL ), '/' ) )
         FROM   DUAL
         CONNECT BY LEVEL <= REGEXP_COUNT( d.value, '\S{20}' )
       ) AS code,
       value
FROM   data d;


CODE
VALUE


001/XYZ/0002/65432178
long words like floxinoxinihilipilification and antidisestablishmentarianism with your code 001/XYZ/0002/65432178

001/XYZ/0002/65432178
i would ?! like / to make * bb a8 001/XYZ/0002/65432178 thank you very much friends

CODE	VALUE
floxinoxinihilipilification	long words like floxinoxinihilipilification and antidisestablishmentarianism with your code 001/XYZ/0002/65432178
001/XYZ/0002/65432178	i would ?! like / to make * bb a8 001/XYZ/0002/65432178 thank you very much friends