如何提取 MySQL 字符串中的第 n 个单词并计算单词出现次数? [英] How to extract the nth word and count word occurrences in a MySQL string?

问题描述

我想要一个像这样的 mysql 查询:

I would like to have a mysql query like this:

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

mysql中所有的regex示例都是用来查询文本是否与表达式匹配，而不是从表达式中提取文本.有这样的语法吗?

All the regex examples in mysql are used to query if the text matches the expression, but not to extract text out of an expression. Is there such a syntax?

推荐答案

以下是针对 OP 的特定问题(提取字符串的第 2 个单词)的建议解决方案，但应注意正如 mc0e 的回答所述，MySQL 不支持开箱即用地提取正则表达式匹配项.如果你真的需要这个，那么你的选择基本上是 1) 在客户端的后处理中进行，或者 2) 安装一个 MySQL 扩展来支持它.

The following is a proposed solution for the OP's specific problem (extracting the 2nd word of a string), but it should be noted that, as mc0e's answer states, actually extracting regex matches is not supported out-of-the-box in MySQL. If you really need this, then your choices are basically to 1) do it in post-processing on the client, or 2) install a MySQL extension to support it.

BenWells 的说法几乎是正确的.根据他的代码，这里有一个稍微调整的版本:

BenWells has it very almost correct. Working from his code, here's a slightly adjusted version:

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

作为一个工作示例，我使用了:

As a working example, I used:

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

这篇关于如何提取 MySQL 字符串中的第 n 个单词并计算单词出现次数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！