如何在Oracle SQL中提取字符串列的所有单词

要实现将字符串列中的每个单词拆分为单独行的需求，以下是两种可行的Oracle SQL方案：

方法一：正则表达式 + CONNECT BY #

WITH sample_data AS (
    SELECT 'extract words' AS title FROM dual
    UNION 
    SELECT 'words in a phrase' FROM dual
)
SELECT TRIM(REGEXP_SUBSTR(title, '[^ ]+', 1, LEVEL)) AS words
FROM sample_data
CONNECT BY LEVEL <= REGEXP_COUNT(title, '[^ ]+')
    AND PRIOR title = title
    AND PRIOR SYS_GUID() IS NOT NULL
ORDER BY words;

说明 #

• REGEXP_COUNT(title, '[^ ]+')：统计每行字符串中的单词总数（以空格分隔）
• CONNECT BY LEVEL <= ...：根据单词数量生成对应行数
• REGEXP_SUBSTR(title, '[^ ]+', 1, LEVEL)：提取每行中第LEVEL位置的单词
• PRIOR SYS_GUID() IS NOT NULL：避免出现循环连接错误

方法二：XMLTABLE（更简洁） #

WITH sample_data AS (
    SELECT 'extract words' AS title FROM dual
    UNION 
    SELECT 'words in a phrase' FROM dual
)
SELECT TRIM(column_value) AS words
FROM sample_data,
     XMLTABLE(('"' || REPLACE(title, ' ', '","') || '"'))
ORDER BY words;

说明 #

• REPLACE(title, ' ', '","')：将空格替换为","，构造符合XML序列格式的字符串
• XMLTABLE(...)：将构造后的字符串拆分为单独行
• TRIM：清理单词前后可能存在的多余空格

执行结果 #

内容的提问来源于stack exchange，提问作者Bigjo