blocks|key|2000875|text|以下是针对OP的特定问题(提取字符串的第二个单词)提出的解决方案，但需要注意的是，正如mc0e的答案所述，在MySQL中不支持实际提取正则表达式匹配。如果你真的需要它，那么你的选择基本上是1)在客户机上进行后处理，或者2)安装一个MySQL扩展来支持它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2000876|2000877|BenWells几乎是正确的。根据他的代码，下面是一个略微调整的版本：|2000878|SUBSTRING(
++sentence,
++LOCATE('+',+sentence)+%2B+CHAR_LENGTH('+'),
++LOCATE('+',+sentence,
++(+LOCATE('+',+sentence)+%2B+1+)+-+(+LOCATE('+',+sentence)+%2B+CHAR_LENGTH('+')+)
)|code-block|syntax|javascript|2000879|作为一个工作示例，我使用：|2000880|SELECT+SUBSTRING(
++sentence,
++LOCATE('+',+sentence)+%2B+CHAR_LENGTH('+'),
++LOCATE('+',+sentence,
++(+LOCATE('+',+sentence)+%2B+1+)+-+(+LOCATE('+',+sentence)+%2B+CHAR_LENGTH('+')+)
)+as+string
FROM+(SELECT+'THIS+IS+A+TEST'+AS+sentence)+temp|2000881|这成功地提取了单词IS|offset|length|style|CODE|2000882|entityMap^0|0|0|0|0|0|0|9|2|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|C|3|D|5|6|7|X|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|Y|8|@]|9|@]|A|$H|I]]|$1|J|3|K|5|6|7|Z|8|@]|9|@]|A|$]]|$1|L|3|M|5|G|7|10|8|@]|9|@]|A|$H|I]]|$1|N|3|O|5|6|7|11|8|@$P|12|Q|13|R|S]]|9|@]|A|$]]|$1|T|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|U|$]]

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.

<hr>

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

<pre><code>SUBSTRING(
 sentence,
 LOCATE(' ', sentence) + CHAR_LENGTH(' '),
 LOCATE(' ', sentence,
 ( LOCATE(' ', sentence) + 1 ) - ( LOCATE(' ', sentence) + CHAR_LENGTH(' ') )
)
</code></pre>

As a working example, I used:

<pre><code>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
</code></pre>

This successfully extracts the word <code>IS</code>

blocks|key|2000894|text|提取句子中第二个单词的Shorter选项：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2000895|SELECT+SUBSTRING_INDEX(SUBSTRING_INDEX('THIS+IS+A+TEST',+'+',++2),+'+',+-1)+as+FoundText|code-block|syntax|javascript|2000896|MySQL+docs+for+SUBSTRING_INDEX|offset|length|2000897|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index^0|0|0|0|U|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Shorter option to extract the second word in a sentence:

<pre><code>SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('THIS IS A TEST', ' ', 2), ' ', -1) as FoundText
</code></pre>

<a href="http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index">MySQL docs for SUBSTRING_INDEX</a>

blocks|key|2000838|text|根据http://dev.mysql.com/的说法，SUBSTRING函数使用起始位置，然后是长度，所以第二个单词的函数肯定是：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2000839|SUBSTRING(sentence,LOCATE('+',sentence),(LOCATE('+',LOCATE('+',sentence))-LOCATE('+',sentence)))|code-block|syntax|javascript|2000840|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring^0|2|L|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

According to <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring" rel="nofollow noreferrer">http://dev.mysql.com/</a> the SUBSTRING function uses start position then the length so surely the function for the second word would be: 

<pre><code>SUBSTRING(sentence,LOCATE(' ',sentence),(LOCATE(' ',LOCATE(' ',sentence))-LOCATE(' ',sentence)))
</code></pre>

blocks|key|1787041|text|不，没有使用正则表达式提取文本的语法。你必须使用普通的string+manipulation+functions。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1787042|或者，从数据库中选择整个值(如果担心数据传输太多，则选择前n个字符)，然后在客户机上使用正则表达式。|1787043|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/string-functions.html^0|R|T|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

No, there isn't a syntax for extracting text using regular expressions. You have to use the ordinary <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html" rel="noreferrer">string manipulation functions</a>.

Alternatively select the entire value from the database (or the first n characters if you are worried about too much data transfer) and then use a regular expression on the client.

blocks|key|2001004|text|正如其他人所说，mysql不提供用于提取子字符串的正则表达式工具。但这并不是说，如果您准备使用用户定义函数扩展mysql，就不能使用它们：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2001005|https://github.com/mysqludf/lib_mysqludf_preg|offset|length|2001006|如果您想分发软件，这可能没有太大帮助，因为这是安装软件的障碍，但对于内部解决方案来说，它可能是合适的。|2001007|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|19|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

As others have said, mysql does not provide regex tools for extracting sub-strings. That's not to say you can't have them though if you're prepared to extend mysql with user-defined functions:

<a href="https://github.com/mysqludf/lib_mysqludf_preg" rel="noreferrer">https://github.com/mysqludf/lib_mysqludf_preg</a>

That may not be much help if you want to distribute your software, being an impediment to installing your software, but for an in-house solution it may be appropriate.

blocks|key|1787298|text|我使用Brendan+Bullen的答案作为我遇到的类似问题的起点，即检索JSON字符串中特定字段的值。然而，正如我在他的回答中所评论的那样，这并不完全准确。如果你的左边界不是像原始问题中那样只是一个空间，那么差异就会增加。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1787299|更正的解决方案：|1787300|SUBSTRING(
++++sentence,
++++LOCATE('+',+sentence)+%2B+1,
++++LOCATE('+',+sentence,+(LOCATE('+',+sentence)+%2B+1))+-+LOCATE('+',+sentence)+-+1
)|code-block|syntax|javascript|1787301|两个差异是SUBSTRING+index参数中的%2B1和length参数中的-1。|1787302|有关“在两个提供的边界之间查找字符串的第一个匹配项”的更一般解决方案：|1787303|SUBSTRING(
++++haystack,
++++LOCATE('<leftBoundary>',+haystack)+%2B+CHAR_LENGTH('<leftBoundary>'),
++++LOCATE(
++++++++'<rightBoundary>',
++++++++haystack,
++++++++LOCATE('<leftBoundary>',+haystack)+%2B+CHAR_LENGTH('<leftBoundary>')
++++)
++++-+(LOCATE('<leftBoundary>',+haystack)+%2B+CHAR_LENGTH('<leftBoundary>'))
)|1787304|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|V|8|@]|9|@]|A|$G|H]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

I used Brendan Bullen's answer as a starting point for a similar issue I had which was to retrive the value of a specific field in a JSON string. However, like I commented on his answer, it is not entirely accurate. If your left boundary isn't just a space like in the original question, then the discrepancy increases.

Corrected solution:

<pre><code>SUBSTRING(
 sentence,
 LOCATE(' ', sentence) + 1,
 LOCATE(' ', sentence, (LOCATE(' ', sentence) + 1)) - LOCATE(' ', sentence) - 1
)
</code></pre>

The two differences are the +1 in the SUBSTRING index parameter and the -1 in the length parameter.

For a more general solution to "find the first occurence of a string between two provided boundaries":

<pre><code>SUBSTRING(
 haystack,
 LOCATE('&lt;leftBoundary&gt;', haystack) + CHAR_LENGTH('&lt;leftBoundary&gt;'),
 LOCATE(
 '&lt;rightBoundary&gt;',
 haystack,
 LOCATE('&lt;leftBoundary&gt;', haystack) + CHAR_LENGTH('&lt;leftBoundary&gt;')
 )
 - (LOCATE('&lt;leftBoundary&gt;', haystack) + CHAR_LENGTH('&lt;leftBoundary&gt;'))
)
</code></pre>

blocks|key|1787026|text|我认为这样的事情是不可能的。你可以使用SUBSTRING函数来提取你想要的部分。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1787027|entityMap^0|J|9|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

I don't think such a thing is possible. You can use <code>SUBSTRING</code> function to extract the part you want.

blocks|key|2000912|text|该字段的值为：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2000913|+"-+DE-HEB+20%25+-+DTopTen+1.2%25"
SELECT+....
SUBSTRING_INDEX(SUBSTRING_INDEX(DesctosAplicados,+'DE-HEB+',++-1),+'-',+1)+DE-HEB+,
SUBSTRING_INDEX(SUBSTRING_INDEX(DesctosAplicados,+'DTopTen+',++-1),+'-',+1)+DTopTen+,

FROM+TABLA+|code-block|syntax|javascript|2000914|结果是：|2000915|++DE-HEB+++++++DTopTEn
++++20%25++++++++++1.2%25|2000916|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

The field's value is: 

<pre><code> "- 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 
</code></pre>

Result is:

<pre><code> DE-HEB DTopTEn
 20% 1.2%
</code></pre>

I would like to have a mysql query like this:

<pre><code>select &lt;second word in text&gt; word, count(*) from table group by word;
</code></pre>

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?

How to extract the nth word and count word occurrences in a MySQL string?

我希望有一个这样的mysql查询：select <second word in text> word, count(*) from table group by word;mysql中的所有正则表达式示例都用于查询文本是否与表达式匹配，而不是用于从表达式中提取文本。有这样的语法吗？

问如何提取MySQL字符串中的第n个单词并统计单词出现的次数？
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何提取MySQL字符串中的第n个单词并统计单词出现的次数？EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何提取MySQL字符串中的第n个单词并统计单词出现的次数？
EN