Empty string return - Oracle

Question

I have a table with a string and several fields separated by | (pipe)

I need to bring each field in a column. I even got a way, the problem is that when a record does not have the field filled the string brings ||.

Detail, i cannot change the original string.

Someone would have a solution in Oracle SQL ??

I did it this way:

CREATE TABLE t_1 ( strings varchar2(4000));
/
INSERT INTO t_1
VALUES ('99|1500001|JOANA PRADO |08227525490|BRA|0||');

INSERT INTO t_1
VALUES ('99|1500002|LUCIANA XIMENES SILVA||BRA|0||');

INSERT INTO t_1
VALUES ('99|1500003|JULIA ROBERTS|44303539805|USA|0||');

COMMIT;

SELECT regexp_substr(strings,'[^|]+',1,1) identificador,
       regexp_substr(strings,'[^|]+',1,2) id,
       regexp_substr(strings,'[^|]+',1,3) nome,
       regexp_substr(strings,'[^|]+',1,4) cpf,
       regexp_substr(strings,'[^|]+',1,5) pais,
       regexp_substr(strings,'[^|]+',1,6) tipo
FROM t_1

Notice that the registration id 1500002 does not have Cpf and then the field of parents ends up occupying the space of the CPF.

Answer 1

If fields can be empty:

SELECT rtrim(regexp_substr(strings,'[^|]*',1,1),'|') AS identificador,
       rtrim(regexp_substr(strings,'[^|]*',1,2),'|') AS id,
       rtrim(regexp_substr(strings,'[^|]*',1,3),'|') AS nome,
       rtrim(regexp_substr(strings,'[^|]*',1,4),'|') AS cpf,
       rtrim(regexp_substr(strings,'[^|]*',1,5),'|') AS pais,
       rtrim(regexp_substr(strings,'[^|]*',1,6),'|') AS tipo
FROM t_1

Basically include the separating letter in the result, and delete it later with RTRIM().