Oracle SQL - Write SQL to use the REGEXP_INSTR function to search for words.

Requirements

Here is the table

Demo

SQL>
SQL> drop table history;

Table dropped.-- ww w  .  java 2 s .  c o m

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>

Write SQL to use the REGEXP_INSTR function to search for words.

Demo

SQL>
SQL> select comments
  2  from   history
  3  where  regexp_like(comments, '([[:alnum:]+[:punct:]]+[[:space:]]+){8,}');

COMMENTS-- from  ww  w.  ja v  a 2s.  c o  m
------------------------------------------------------------
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 Quiz