Oracle SQL - REGEXP_SUBSTR to search text between parentheses

  1. HOME
  2. Oracle SQL
  3. Select
  4. Regular Expressions

Introduction

The following code searches for comments between parentheses, using the REGEXP_SUBSTR function.

The search pattern looks for a left parenthesis, followed by at least one character not equal to a right parenthesis, followed by a right parenthesis.

You need the backslash character (\) to suppress the special meaning of the parentheses.

Demo

SQL>
SQL>-- w w w .  j  ava2  s. c om
SQL> drop table history;

Table dropped.

SQL> create table history(
  2  comments   VARCHAR2(60)) ;
SQL>
SQL> insert into history values ('this is a test');
SQL> insert into history values ('salary reduction this is a test');
SQL> insert into history values ('this is a test this is a test Transfer to sales department');
SQL> insert into history values ('Not a great trainer; this is a test this is a test this is a test!');
SQL> insert into history values ('Senior sales this is a test this is a test this is a test?');
SQL> insert into history values ('Turns out to be slightly disappointing this is a test this is a test');
SQL> insert into history values ('Transfer to human resources; salary raise this is a test');
SQL> insert into history values ('Salary reduction 550 this is a test this is a test');
SQL> insert into history values ('Founder and first employee of the company');
SQL> insert into history values ('Accounting established this is a test this is a test');
SQL> insert into history values ('Project for the ACCOUNTING department this is a test');
SQL>
SQL> select comments
  2  ,      regexp_substr(comments, '\([^\)]+\)') as substring
  3  from   history
  4  where  comments like '%(%';

no rows selected

SQL>

Related Topic