CODEWITHSUNDEEP
sqlregexoracle-database
Syed Ali
Syed Ali

Reputation: 1

How would I use regular expression to extract words outside of brackets in Oracle?

I want to extract the value outside of brackets from a column.

Query:

select dwn_notes from down_time;

Query Result:

4 hour scheduled downtime (dball, 01/04/2019 09:14)
4 hour scheduled downtime (dball, 01/04/2019 09:14)
4 hour scheduled downtime (dball, 01/04/2019 09:14)
4 hour scheduled downtime (dball, 01/04/2019 09:14)

Upvotes: 0

Views: 603

Answers (4)

Nikhil
Nikhil

Reputation: 3950

this will work:

select regexp_replace(down_notes,'[(]{1}(.)*[)]{1}','') from Table1;

check fiddle:http://sqlfiddle.com/#!4/b52fd/6

Upvotes: 1

Gary_W
Gary_W

Reputation: 10360

You can use REGEXP_REPLACE() to capture the part of the string before the parenthesis in a group and the part after the group, then return just those groups. Make sure to use a good set of test data, including values you don't expect.

with down_time(id, down_notes) as (
  select 1, '1 hour scheduled downtime (dball, 01/04/2019 09:14)' from dual union all
  select 2, '2 hour scheduled downtime (dball, 01/05/2019 09:14) with more text after' from dual union all
  select 3, '' from dual union all
  select 4, '(dball, 01/04/2019 09:14)' from dual union all
  select 5, '5 hour scheduled downtime' from dual
)
select id, trim(regexp_replace(down_notes, '^(.*?)\s*\(.*\)\s*(.*?)$', '\1 \2')) down_notes
from down_time;

Be advised that if REGEXP_REPLACE() does not find a match, the string passed in will be returned. This is different from REGEXP_SUBSTR(), where it returns NULL if it can't find a match. You may have to allow for this in returned values.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Considering the part of the string upto the opening parentheses is enough for your case :

select regexp_substr(dwn_notes,'([^\()])+') as dwn_notes
  from dwn_time;

If you need everything outside of the paretheses, then consider :

select concat(
               regexp_substr(dwn_notes,'([^\(.*\))])+'), 
               regexp_substr(dwn_notes,'([^\(.*\))])+$')
              ) as dwn_notes
  from dwn_time;

Demo

Upvotes: 0

Andrew
Andrew

Reputation: 8703

Based on your sample data, you can just use instr to locate the first open paren, and substring that out:

select dwn_notes,
substr(dwn_notes,1,instr(dwn_notes,'(') -1)
from
down_time

Fiddle

Upvotes: 1

Related Questions