Regex for retrieving substring between a left parenthesis and a hyphen-CodePudding

I'm a noob at Regex, I am working in Oracle SQL. using Regex_substr.

If I want to get the text between a left parenthesis and a hyphen what would that look like?

for example:

my_branch (country-code > province-code)

I only want to retrieve the substring 'country' in (country-code > province-code)

How would it look like?

CodePudding user response：

Look for:

an opening bracket \(; then
.*? zero-or-more of any character wrapped in a capturing group ()
until the first - hyphen.

Then extract the substring from the first capturing group.

SELECT REGEXP_SUBSTR(
         column_name,
         '\((.*?)\-',
         1,
         1,
         NULL,
         1
       ) AS substring
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (column_name) AS
SELECT 'my_branch (country-code > province-code)' FROM DUAL;

Outputs:

SUBSTRING

country

SUBSTRING
country

db<>fiddle here

CodePudding user response：

With such a simple requirement, substr instr are also simple enough and return desired result (and, for large data sets, behave better (faster)). Or, if you must, regexp_substr isn't too complex either. Once again: with simple sample data you posted.

SQL> with test (col) as
  2    (select 'my_branch (country-code > province-code)' from dual)
  3  select substr(col, instr(col, '(')   1,
  4                     instr(col, '-') - instr(col, '(') - 1
  5               ) result_1,
  6         ltrim(regexp_substr(col, '\(\w '), '(') result_2
  7  from test;

RESULT_1   RESULT_2
---------- ----------
country    country

SQL>