如何提取第n个单词并计算MySQL字符串中出现的单词? [英] 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中的所有正则表达式示例都用于查询文本是否与表达式匹配，而不是从表达式中提取文本.有这样的语法吗?

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的特定问题(提取字符串的第二个单词)的建议解决方案，但应注意正如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

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