capturing value after @ separated string in different columns in oracle

Question

I need the third value, fourth value, fifth value and seventh value after @ separated string . if no value present between @@ ,it should be also considered and value needed as null in oracle. i need to use this value from a select statement

ex :N@@N@14/12/2023@WAN HAI 325@@05/01/2024@19/01/2024@29/12/2023@@@@

third value after @ needed is 14/12/2023 , Fourth value -- WAN HAI 325 ,fifth value -null ,seventhvalue -- 19/01/2024

ex:N@@N@06/10/23@@2310206@17/11/23@30/11/2023@11/10/23@@@@@@@@

value after third @ needed is 6/10/2023 , Fourth value --null,fifth value -2310206,seventhvalue -- 30/11/2023

Regards sathish

Answer 1

One option is to use combination of substr + instr functions.

Sample data:

SQL> with test (col) as
  2    (select 'N@@N@14/12/2023@WAN HAI 325@@05/01/2024@19/01/2024@29/12/2023@@@@' from dual union all
  3     select 'N@@N@06/10/23@@2310206@17/11/23@30/11/2023@11/10/23@@@@@@@@'       from dual)

Query:

  4  select substr(col, instr(col, '@', 1, 3) + 1,
  5                     instr(col, '@', 1, 4) - instr(col, '@', 1, 3) - 1
  6               ) val_3,
  7         --
  8         substr(col, instr(col, '@', 1, 4) + 1,
  9                     instr(col, '@', 1, 5) - instr(col, '@', 1, 4) - 1
 10               ) val_4,
 11         --
 12         substr(col, instr(col, '@', 1, 5) + 1,
 13                     instr(col, '@', 1, 6) - instr(col, '@', 1, 5) - 1
 14               ) val_5,
 15         --
 16         substr(col, instr(col, '@', 1, 7) + 1,
 17                     instr(col, '@', 1, 8) - instr(col, '@', 1, 7) - 1
 18               ) val_7
 19  from test;

VAL_3           VAL_4           VAL_5           VAL_7
--------------- --------------- --------------- ---------------
14/12/2023      WAN HAI 325                     19/01/2024
06/10/23                        2310206         30/11/2023

SQL>