blocks|key|893770|text|提取句子中第二个单词的Shorter选项：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|893771|SELECT+SUBSTRING_INDEX(SUBSTRING_INDEX('THIS+IS+A+TEST',+'+',++2),+'+',+-1)+as+FoundText|code-block|syntax|javascript|893772|MySQL+docs+for+SUBSTRING_INDEX|offset|length|893773|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|1108668|text|根据http://dev.mysql.com/的说法，SUBSTRING函数使用起始位置，然后是长度，所以第二个单词的函数肯定是：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1108669|SUBSTRING(sentence,LOCATE('+',sentence),(LOCATE('+',LOCATE('+',sentence))-LOCATE('+',sentence)))|code-block|syntax|javascript|1108670|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|893661|text|不，没有使用正则表达式提取文本的语法。你必须使用普通的string+manipulation+functions。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|893662|或者，从数据库中选择整个值(如果担心数据传输太多，则选择前n个字符)，然后在客户机上使用正则表达式。|893663|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|893892|text|正如其他人所说，mysql不提供用于提取子字符串的正则表达式工具。但这并不是说，如果您准备使用用户定义函数扩展mysql，就不能使用它们：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|893893|https://github.com/mysqludf/lib_mysqludf_preg|offset|length|893894|如果您想分发软件，这可能没有太大帮助，因为这是安装软件的障碍，但对于内部解决方案来说，它可能是合适的。|893895|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|1108937|text|我使用Brendan+Bullen的答案作为我遇到的类似问题的起点，即检索JSON字符串中特定字段的值。然而，正如我在他的回答中所评论的那样，这并不完全准确。如果你的左边界不是像原始问题中那样只是一个空间，那么差异就会增加。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1108938|更正的解决方案：|1108939|SUBSTRING(
++++sentence,
++++LOCATE('+',+sentence)+%2B+1,
++++LOCATE('+',+sentence,+(LOCATE('+',+sentence)+%2B+1))+-+LOCATE('+',+sentence)+-+1
)|code-block|syntax|javascript|1108940|两个差异是SUBSTRING+index参数中的%2B1和length参数中的-1。|1108941|有关“在两个提供的边界之间查找字符串的第一个匹配项”的更一般解决方案：|1108942|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>'))
)|1108943|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|1108588|text|我认为这样的事情是不可能的。你可以使用SUBSTRING函数来提取你想要的部分。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1108589|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|893815|text|该字段的值为：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|893816|+"-+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|893817|结果是：|893818|++DE-HEB+++++++DTopTEn
++++20%25++++++++++1.2%25|893819|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?

数据库

JSON

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

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

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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