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;

Answer 2

This method takes the result of extracting the number of words you want, then reduces multiple spaces to one:

select trim(regexp_replace(regexp_substr('Hello         world this is a test etc', '(([^ ]*)( |$)*){3}'), ' +', ' '))
from dual;

EDIT: This is getting ugly, but wrapped a TRIM() around it to get rid of the trailing space (the one after the last word selected).