Oracle SQL - REGEXP_INSTR to search text with nine or more words

Introduction

The following code shows how to use the REGEXP_INSTR function to search for history comments with nine or more words.

It looks for at least nine nonempty (+) substrings that do not contain spaces [^ ].

Demo

SQL>
SQL>--   w  w w. j ava  2  s  . c o m
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  from   history
  3  where  regexp_instr(comments, '[^ ]+', 1, 9) > 0;

COMMENTS
------------------------------------------------------------
this is a test this is a test Transfer to sales department
Senior sales this is a test this is a test this is a test?
Transfer to human resources; salary raise this is a test
Salary reduction 550 this is a test this is a test
Accounting established this is a test this is a test
Project for the ACCOUNTING department this is a test

6 rows selected.

SQL>

Related Topic