Split string by delimiter in Oracle with regular expressions

Question

I need to write script in Oracle to split strings 'BP/593/00294' and 'NC//12345' by / character in a query to have values in separate columns.

I was thinking something like:

select regexp_substr(mystr, '[^/]+') as col1, 
regexp_substr(c.usertext21,'[^/]+',1,2) as col2
from mytable

but in col2 I loose empty string value from second string. I need to keep every value from both strings. This should be the result:

<table>
<th>col1</th>
<th>col2</th>
<th>col3</th>
<tr>
  <td>BP</td>
  <td>593</td>
  <td>00294</td>
</tr>
<tr>
  <td>NC</td>
  <td></td>
  <td>12345</td>
</tr>
</table>

Any help would be appreciated. Thanks

Answer 1

If you have unknown number of splits to be catched and you want each split to be a separate row, you can use connect by clause:

with example as (
   select 'NC//12345/12qwe/////12sadf' text from dual
)
 select regexp_substr(e.text, '[^/]+', 1, LEVEL) col
   from example e
connect by regexp_instr(e.text, '[/]+', 1, LEVEL - 1) > 0

Demo

Answer 2

You may capture 0 or more chars other than / at the start of string or after /:

select 
  regexp_substr('BP/593/00294', '(^|/)([^/]*)') as col1,
  regexp_substr('BP/593/00294', '(^|/)([^/]*)', 1, 2, null, 2)  as col2,
  regexp_substr('BP/593/00294', '(^|/)([^/]*)', 1, 3, null, 2)  as col3
from dual

See the online demo.

Details

• (^|/) - Capturing group 1: start of string or /
• ([^/]*) - Capturing group 2: any 0 or more chars other than /.

Note the 2 argument that extracts Group 2 value. See the regex demo.