I am struggle with regex to split spring into columns in Oracle database.

select (REGEXP_SUBSTR(replace('1:::9999', ' ',''), '[^: ] ', 1, 4)) from dual;

I need to obtain 4th value from that string as a column value, sometimes values at position 2,3 are empty and my query doesn't work. I am trying to figure out what regex will work

CodePudding user response：

You can use

select (REGEXP_SUBSTR(replace('1:::9999', ' ',''), '([^: ]*)(:|$)', 1, 4, 'i', 1)) from dual;

Here, the ([^: ]*)(:|$) matches

• ([^: ]*) - Group 1: any zero or more chars other than : and space
• (:|$) - Group 2, either : or end of string.

CodePudding user response：

You do not need a (slower) regex for this task, use simple substr/instr functions:

with input_(val) as (
  select '1:::9999' from dual
  union all
  select '1:2::' from dual
  union all
  select '1:2::3:5' from dual
)
, replaced as (
  select input_.*, replace(val, ' ', '') as val_replaced
  from input_
)
select
  val,
  substr(
    val_replaced,
    /*Locate the first occurrence of a colon and get a substring ...*/
    instr(val_replaced, ':', 1, 3)   1,
    /*.. until the end, if the next colon is absent, or until the next colon*/
    nvl(nullif(instr(val_replaced, ':', 1, 4), 0), length(val_replaced)   1) - instr(val_replaced, ':', 1, 3) - 1
  ) as col
from replaced

fiddle with performance difference.