Retrieving first X words from a string in Oracle Select

Question

I need to select the first X words in a string, where x can be any number from 0-100. Is there an easy way to do this? I found the following example to select the first 2 words from a string:

select regexp_replace('Hello world this is a test', '(\w+ \w+).*$','\1') as first_two
from dual

How would I select the first X words from a string where X can be a number from 0-100?

Answer 1

Selecting the first four words:

select
   regexp_replace(
     'Hello world this is a test etc',
     '(((\w+)\s){4}).*',  -- Change 4 to wanted number of words here!
     '\1'
   )
   from dual;

Edit

The above solution only works if the words are seperated by exactly one white space character. If the words are seperated by one or more white space characters, the \s must be extended to \s+:

select
   regexp_replace(
     'Hello    world   this   is a   test     etc',
     '(((\w+)\s+){4}).*',  -- Change 4 to wanted number of words here!
     '\1'
   )
   from dual;