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 [^ ].
SQL> SQL>-- w w w. ja v a 2 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 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>