Oracle 18c

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

create table test(stringval varchar(1000))
insert into test values('RANDOM(NUL)THIS_SHOULD_BE_SPLIT(NUL)~THIS_IS_NEW(NUL)STRING(NUL)~THIS_IS_ANOTHER_STRING(NUL)')
1 rows affected

SELECT REGEXP_SUBSTR(replace(stringval, '(NUL)~', '~'),'[^~]+', 1, LEVEL) 
       AS error_code 
FROM test
CONNECT BY REGEXP_SUBSTR(stringval, '[^~]+',1,LEVEL) IS NOT NULL;


ERROR_CODE


RANDOM(NUL)THIS_SHOULD_BE_SPLIT

THIS_IS_NEW(NUL)STRING

THIS_IS_ANOTHER_STRING(NUL)