6

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?

Gary_W
  • 9,933
  • 1
  • 22
  • 40
Robert Smith
  • 779
  • 1
  • 10
  • 28
  • In what form do you need the result? As a single string? Or one column per word? Or one row per word? – Lukas Eder Jun 03 '15 at 13:19
  • As a single string. Rene's solution below worked except I have some strings that have two spaces in between and it is failing on those. It was not his fault since I did not mention that. – Robert Smith Jun 03 '15 at 13:42
  • @Robert Smith: I have updated my answer so that it works with two (or more) spaces between the words. – René Nyffenegger Jun 03 '15 at 15:51

3 Answers3

4

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;
René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
1

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).

Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • It is working but the only little detail is that it's leaving an extra space at the end of the string. – Robert Smith Jun 03 '15 at 17:57
  • Ok I added a TRIM() call around it to remove the trailing space and updated my post. P.S. Rene's solution needs that too. – Gary_W Jun 03 '15 at 18:11
  • By the way, use a proportional font and wrap the selected string in square brackets like this to make it easy to see the space: select '[' || my_string || ']' from .... – Gary_W Jun 03 '15 at 18:27
0

this would do it, but it may be a bit inelegant, replace "2" with the number of words to find

select substr('this is a number of words',1,instr('this is a number of words',' ',1,2))
from dual

does assume words always end with a space

davegreen100
  • 2,055
  • 3
  • 13
  • 24