CODEWITHSUNDEEP
sqloracle-databaseselect
Stanley Mungai
Stanley Mungai

Reputation: 4150

Read a pipe delimited field into dynamic fields oracle

I am facing a challenge when reporting from my audit table which has a modified fields data in this format:

MODIFIED_FIELDS_DATA
Name|Stanley|mike|Location|London|Moscow|Age|20|24

That field shows the modified field, Old value and the new value. For example this means that the field Name has been changed from Stanley to Mike, Location has been changed from London to Moscow and so on..

I intent to read the field and report it :

FIELD| OLD_VALUE| NEW_VALUE |FIELD2 OLD_VALUE2 | NEW_VALUE2 |FIELD3 OLD_VALUE3| NEW_VALUE3
Name | Stanley| Mike  Location|London|Moscow|Age|20|24

Anyone who can give me ideas how to achieve this?

Upvotes: 1

Views: 58

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175636

You can use something like:

SqlFiddle

with temp as
(
    select 1 AS id,'Name|Stanley|mike|Location|London|Moscow|Age|20|24' AS col  from dual
)
select 
  id
  ,trim(regexp_substr(t.col, '[^|]+', 1, 1)) AS FIELD1 
  ,trim(regexp_substr(t.col, '[^|]+', 1, 2)) AS OLD_VALUE 
  ,trim(regexp_substr(t.col, '[^|]+', 1, 3)) AS NEW_VALUE 
  ,trim(regexp_substr(t.col, '[^|]+', 1, 4)) AS FIELD2 
  ,trim(regexp_substr(t.col, '[^|]+', 1, 5)) AS OLD_VALUE2 
  ,trim(regexp_substr(t.col, '[^|]+', 1, 6)) As NEW_VALUE2 
  ,trim(regexp_substr(t.col, '[^|]+', 1, 7)) AS FIELD3 
  ,trim(regexp_substr(t.col, '[^|]+', 1, 8)) As OLD_VALUE3 
  ,trim(regexp_substr(t.col, '[^|]+', 1, 9)) AS NEW_VALUE3
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.col, '[^|]+'))  + 1) as sys.OdciNumberList)) levels
GROUP BY id;

EDIT

You example Name|Stanley||Location|Moscow||London doesn't make sense because it has 7 values not 6 but you can use:

SqlFiddleDemo

select 1 AS id,REPLACE('Name|Stanley||Location|Moscow||London','||', '| |') AS col  from dual

to handle empty values.

Upvotes: 1

Related Questions