facing problem in spliting string in oracle sql

Question

I want to split below example in oracle. I tried using instr function, but its very complicated in INSTR. Any leads would very helpful.

I have tried using instr and substring.

Example:

Spend Cap Chargeable Voice Bar|15/05/2019 07:45:17|International Bar c3|15/05/2019 07:45:17|International Bar c5|15/05/2019 07:45:17|International Bar c6|15/05/2019 07:45:17|ROW Data Roaming Bar|15/05/2019 07:45:17|MMS service|15/05/2019 07:45:17|RLAH Chargeable Calls Bar|15/05/2019 07:45:17|ROW O2 Travel Inclusive|15/05/2019 07:45:17|Premium MO / International SMS Bar|15/05/2019 07:45:17

The result should be as below:

Result:

Spend Cap Chargeable Voice Bar|15/05/2019 07:45:17
International Bar c3|15/05/2019 07:45:17
International Bar c5|15/05/2019 07:45:17
International Bar c6|15/05/2019 07:45:17
ROW Data Roaming Bar|15/05/2019 07:45:17
MMS service|15/05/2019 07:45:17
RLAH Chargeable Calls Bar|15/05/2019 07:45:17
ROW O2 Travel Inclusive|15/05/2019 07:45:17
Premium MO / International SMS Bar|15/05/2019 07:45:17

Answer 1

Assuming you're starting from a single long string, you could use regular expressions and a hierarchical query to split the string into pairs of values:

select regexp_substr(your_string, '(.*?)(\||$)', 1, (2*level) - 1, null, 1) as part_one,
  regexp_substr(your_string, '(.*?)(\||$)', 1, 2*level, null, 1) as part_two
from your_table
connect by level <= ceil(regexp_count(your_string, '\|') / 2);

PART_ONE                                 PART_TWO            
---------------------------------------- --------------------
Spend Cap Chargeable Voice Bar           15/05/2019 07:45:17 
International Bar c3                     15/05/2019 07:45:17 
International Bar c5                     15/05/2019 07:45:17 
International Bar c6                     15/05/2019 07:45:17 
ROW Data Roaming Bar                     15/05/2019 07:45:17 
MMS service                              15/05/2019 07:45:17 
RLAH Chargeable Calls Bar                15/05/2019 07:45:17 
ROW O2 Travel Inclusive                  15/05/2019 07:45:17 
Premium MO / International SMS Bar       15/05/2019 07:45:17 

and either then handle them as pairs of values (probably converting the second part to a date data type) or concatenate them back together:

select regexp_substr(your_string, '(.*?)(\||$)', 1, (2*level) - 1, null, 1)
  ||'|'|| regexp_substr(your_string, '(.*?)(\||$)', 1, 2*level, null, 1) as combined
from your_table
connect by level <= ceil(regexp_count(your_string, '\|') / 2);    

COMBINED                                          
------------------------------------------------------------
Spend Cap Chargeable Voice Bar|15/05/2019 07:45:17
International Bar c3|15/05/2019 07:45:17
International Bar c5|15/05/2019 07:45:17
International Bar c6|15/05/2019 07:45:17
ROW Data Roaming Bar|15/05/2019 07:45:17
MMS service|15/05/2019 07:45:17
RLAH Chargeable Calls Bar|15/05/2019 07:45:17
ROW O2 Travel Inclusive|15/05/2019 07:45:17
Premium MO / International SMS Bar|15/05/2019 07:45:17

db<>fiddle

Oracle 10g doesn't know REGEXP_COUNT

Good point; in which case change the last line to use this instead:

connect by regexp_instr(your_string, '\|', 1, (2*level) - 1) > 0;

db<>fiddle

The hierarchical query will get confused if you try to run this against more than one row of data at a time; there are tricks/hacks to get around that but it isn't clear if those are necessary for what you are actually doing.

Answer 2

Yet another, step-by-step option:

SQL> with
  2  test as
  3    (select 'Spend Cap Chargeable Voice Bar|15/05/2019 07:45:17|International Bar c3|15/05/2019 07:45:17|Internationa
l Bar c5|15/05/2019 07:45:17|International Bar c6|15/05/2019 07:45:17|ROW Data Roaming Bar|15/05/2019 07:45:17|MMS servi
ce|15/05/2019 07:45:17|RLAH Chargeable Calls Bar|15/05/2019 07:45:17|ROW O2 Travel Inclusive|15/05/2019 07:45:17|Premium
 MO / International SMS Bar|15/05/2019 07:45:17' col
  4     from dual),
  5  inter as
  6    -- split them to rows, pipe being the row separator
  7    (select level lvl,
  8            regexp_substr(col, '[^|]+', 1, level) val
  9     from test
 10     connect by level <= length(col) - length(replace(col, '|', '')) + 1
 11    ),
 12  pair1 as
 13    -- odd rows
 14    (select lvl, val
 15     from inter
 16     where mod(lvl, 2) = 1
 17    ),
 18  pair2 as
 19    -- even rows; decrease LVL by 1 for join purposes
 20    (select lvl - 1 lvl, val
 21     from inter
 22     where mod(lvl, 2) = 0
 23    )
 24    -- join rows and create the result
 25  select a.val || '|' || b.val result
 26  from pair1 a join pair2 b on a.lvl = b.lvl
 27  order by a.lvl;

RESULT
--------------------------------------------------------------------------------
Spend Cap Chargeable Voice Bar|15/05/2019 07:45:17
International Bar c3|15/05/2019 07:45:17
International Bar c5|15/05/2019 07:45:17
International Bar c6|15/05/2019 07:45:17
ROW Data Roaming Bar|15/05/2019 07:45:17
MMS service|15/05/2019 07:45:17
RLAH Chargeable Calls Bar|15/05/2019 07:45:17
ROW O2 Travel Inclusive|15/05/2019 07:45:17
Premium MO / International SMS Bar|15/05/2019 07:45:17

9 rows selected.

SQL>