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

Wiktor Stribiżew · Accepted Answer · 2019-02-25 15:48:45Z

3

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.

answered Feb 25, 2019 at 15:48

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dj.milojevic Over a year ago

Works like a charm! Thank you so much!

piezol · Accepted Answer · 2019-02-25 16:37:03Z

2

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

answered Feb 25, 2019 at 16:37

piezol

9636 silver badges25 bronze badges

Collectives™ on Stack Overflow

Split string by delimiter in Oracle with regular expressions

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related