Empty string return - Oracle

Question

Empty string return - Oracle

Navigation

#1 by (1 votes)

0

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

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

Detail, I can not change the original string.

Would anyone have a solution in Oracle SQL ??

I did this:

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

Note that registration id 1500002 does not have cpf and there the parent field ends up occupying the space of the CPF.

sql oracle

asked by anonymous 07.04.2016 / 21:05

1 answer

Problem clicking the button, called ajax Because the input does not appear in the result of function php

score 1 · Accepted Answer

If the 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

It basically includes the separator letter in the result, and then excludes it with RTRIM() .