how to extract all words from a string column in sql oracle

Question

I want to extract all words of a column into a new column in SQL Oracle. For example I have the sample data

select 'extract words' as title from dual
union 
select 'words in a phrase' from dual

That gives me this table

Title
words in a phrase
extract words

I want as result:

Words
words
in
a
phrase
extract
words

Does this answer your question? [Splitting string into multiple rows in Oracle](https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle) — nbk, Mar 10 '23 at 20:34

score 0 · Answer 1 · answered Mar 10 '23 at 20:27

Here's one method

SELECT title, seq,RTRIM(SUBSTR(title,INSTR(' '||title,' ',1,seq),INSTR(' '||title||' ',' ',1,seq+1) - INSTR(' '||title,' ',1,seq)))
  FROM (select 'extract words' as title from dual
         union 
         select 'words in a phrase' from dual),
       (SELECT ROWNUM seq FROM dual  CONNECT BY LEVEL < 100) x
 WHERE INSTR(' '||title,' ',1,seq) > 0       
 ORDER BY 1,2

score 0 · Answer 2 · answered Mar 10 '23 at 20:31

Very basically:

select csv_id, regexp_substr (
         csv_text,
         '[^,]+',
         1,
         rn
       ) val
from   csvs
cross  join lateral (
  select level rn from dual
  connect by level <=   
    length ( csv_text ) - length ( replace ( csv_text, ',' ) ) + 1
);

Here is exhaustive guide on how to do it.

Gary_W · Answer 3 · 2023-03-10T21:24:06.693

Yet another take. You could always select 'level' as well, say after the 'id' and see the word's position in the title if you wished.

This uses the with clause to just set up the test data. Then for each row, "loop" through each word using connect by where the word's characters are followed by a space or the end of the line. It loops the number of occurrences of a space in the line plus one for the last word.

with tbl(id, title) as (
  select 1, 'extract words' as title from dual union all 
  select 2, 'words in a phrase' from dual
)
select id, regexp_substr(title, '(.*?)( |$)', 1, level, NULL, 1) as words
from tbl
connect by level <= regexp_count(title, ' ') + 1
  and prior id = id
  and prior sys_guid() is not null;


        ID WORDS            
---------- -----------------
         1 extract          
         1 words            
         2 words            
         2 in               
         2 a                
         2 phrase           

6 rows selected.

how to extract all words from a string column in sql oracle

3 Answers3