如何从MySQL字符串中提取第n个单词并计算单词出现次数？

Question

我很愿意有一个像这样的MySQL查询：

select <second word in text> word, count(*) from table group by word;

MySQL中的所有正则表达式示例都用于查询文本是否与表达式匹配，但并不用于从表达式中提取文本。是否有这样的语法？

- Noam

Answer 1

以下是针对OP的具体问题（提取字符串的第二个单词）的一个解决方案，但需要注意，正如mc0e的答案所述，实际上在MySQL中不支持直接提取正则表达式匹配项。如果您确实需要这样做，则选择基本上是1）在客户端进行后处理，或2）安装MySQL扩展以支持它。

---

BenWells几乎正确。根据他的代码，这里是稍微调整过的版本：

SUBSTRING(
  sentence,
  LOCATE(' ', sentence) + CHAR_LENGTH(' '),
  LOCATE(' ', sentence,
  ( LOCATE(' ', sentence) + 1 ) - ( LOCATE(' ', sentence) + CHAR_LENGTH(' ') )
)

作为一个工作范例，我使用了：

SELECT SUBSTRING(
  sentence,
  LOCATE(' ', sentence) + CHAR_LENGTH(' '),
  LOCATE(' ', sentence,
  ( LOCATE(' ', sentence) + 1 ) - ( LOCATE(' ', sentence) + CHAR_LENGTH(' ') )
) as string
FROM (SELECT 'THIS IS A TEST' AS sentence) temp

这成功地提取了单词IS

- Brendan Bullen

---

是的，我错过了句子的引用和定位加1的部分。我并不在意“被采纳的答案”，我只是想帮助别人。 - BenWells

可以将其变得更加通用，不仅限于空格，并且添加LENGTH以匹配分隔符后面的值，例如 LOCATE('<string> ', sentence) + STRLEN('<string>')。 - Noam

除了它实际返回“IS”（不正确）之外，它几乎完美。请参见下面的答案。 - Hypolite Petovan

我同意@HypolitePetovan的观点，这个答案略有不准确，因为它返回了3个字符而不是2个。它的位置也不正确。我建议进行编辑，包括添加CHAR_LENGTH以正确定位和确定正确的长度。当处理空格时很困难，但在整个选择上运行CHAR_LENGTH显示它返回了3个字符。 - doz87

Answer 2

提取句子中第二个单词的更短选项：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('THIS IS A TEST', ' ',  2), ' ', -1) as FoundText

SUBSTRING_INDEX的MySQL文档

- Damien Goor

Answer 3

根据 http://dev.mysql.com/，SUBSTRING 函数使用起始位置和长度参数。因此，第二个单词的函数应该是什么：

SUBSTRING(sentence,LOCATE(' ',sentence),(LOCATE(' ',LOCATE(' ',sentence))-LOCATE(' ',sentence)))

- BenWells

Answer 4

不，没有使用正则表达式提取文本的语法。您必须使用普通的字符串操作函数。

或者从数据库中选择整个值（如果您担心数据传输过多，则选择前n个字符），然后在客户端上使用正则表达式。

- Mark Byers

Answer 5

我使用 Brendan Bullen 的答案作为一个类似问题的起点，该问题是检索 JSON 字符串中特定字段的值。然而，就像我在他的答案中评论的那样，它并不完全准确。如果您的左边界不仅仅是原始问题中的空格，那么差异会增加。

更正的解决方案：

SUBSTRING(
    sentence,
    LOCATE(' ', sentence) + 1,
    LOCATE(' ', sentence, (LOCATE(' ', sentence) + 1)) - LOCATE(' ', sentence) - 1
)

这两个差别在 SUBSTRING 索引参数中的 +1 和长度参数中的 -1。

要更一般化地解决“在两个提供的边界之间查找字符串的第一个出现”的问题：

SUBSTRING(
    haystack,
    LOCATE('<leftBoundary>', haystack) + CHAR_LENGTH('<leftBoundary>'),
    LOCATE(
        '<rightBoundary>',
        haystack,
        LOCATE('<leftBoundary>', haystack) + CHAR_LENGTH('<leftBoundary>')
    )
    - (LOCATE('<leftBoundary>', haystack) + CHAR_LENGTH('<leftBoundary>'))
)

- Hypolite Petovan

Answer 6

如其他人所说，mysql不提供提取子串的正则表达式工具。但这并不意味着你不能使用它们，只要你愿意使用用户定义的函数来扩展mysql：

https://github.com/mysqludf/lib_mysqludf_preg

如果你想分发你的软件，那么这可能帮助不大，因为这会影响安装你的软件，但对于内部解决方案可能是合适的。

- mc0e

Answer 7

我认为这种事情是不可能的。你可以使用SUBSTRING函数来提取你想要的部分。

- user483085

Answer 8

我的自制正则表达式替换函数可以用于此。

演示

请参见这个 DB-Fiddle 演示, 它从一首著名的十四行诗中返回第二个单词（“I”）及其出现次数（1）。

SQL

假设使用MySQL 8或更高版本（以允许使用公共表达式），以下语句将返回第二个单词和它的出现次数。

WITH cte AS (
     SELECT digits.idx,
            SUBSTRING_INDEX(SUBSTRING_INDEX(words, '~', digits.idx + 1), '~', -1) word
     FROM
     (SELECT reg_replace(UPPER(txt),
                         '[^''’a-zA-Z-]+',
                         '~',
                         TRUE,
                         1,
                         0) AS words
      FROM tbl) delimited
     INNER JOIN
     (SELECT @row := @row + 1 as idx FROM 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2, 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3, 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4, 
      (SELECT @row := -1) t5) digits
     ON LENGTH(REPLACE(words, '~' , '')) <= LENGTH(words) - digits.idx)
SELECT c.word,
       subq.occurrences
FROM cte c
LEFT JOIN (
  SELECT word,
         COUNT(*) AS occurrences
  FROM cte
  GROUP BY word
) subq
ON c.word = subq.word
WHERE idx = 1; /* idx is zero-based so 1 here gets the second word */

解释

上述 SQL 中使用了一些技巧，需要进行一些认证。首先，正则表达式替换器用于替换所有连续的非单词字符块 - 每个字符都被单个波浪号 (~) 字符替换。 注意：如果文本中可能出现波浪号，则可以选择其他字符。

然后使用 this answer 中的技术将带有分隔值的字符串转换为单独的行值。它与 this answer 中的聪明技巧相结合，用于生成由一系列递增数字组成的表：在这种情况下是 0-10,000。

- Steve Chambers

Answer 9

该字段的值为：

 "- DE-HEB 20% - DTopTen 1.2%"
SELECT ....
SUBSTRING_INDEX(SUBSTRING_INDEX(DesctosAplicados, 'DE-HEB ',  -1), '-', 1) DE-HEB ,
SUBSTRING_INDEX(SUBSTRING_INDEX(DesctosAplicados, 'DTopTen ',  -1), '-', 1) DTopTen ,

FROM TABLA 

结果是：

  DE-HEB       DTopTEn
    20%          1.2%

- Antonio Rivera