使用 MySQL 计算字符串中字符的出现次数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14157480/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count occurrences of character in a string using MySQL
提问by Pavlos
Example Words: a, akkka, akokaa, kokoko, kakao, oooaooa, kkako, kakaoa
示例词：a, akkka, akokaa, kokoko, kakao, oooaooa, kkako, kakaoa
I need the regexp witch gives words with 2 or less 'a' but not the words without 'a'
我需要正则表达式女巫给出 2 个或更少 'a' 的单词，而不是没有 'a' 的单词
Result: a, akka, kakao, oooaooa, kkako
结果：a、akka、kakao、ooooooa、kkako
Ok actually I am using:
好的，实际上我正在使用：
SELECT word FROM dictionary_gr WHERE word REGEXP 'λ{2,3}' LIMIT 0 , 30
this returns 0 lines there are words with 2 λ's and 3 λ's
这将返回 0 行，其中包含 2 个 λ 和 3 个 λ 的单词
回答by Woot4Moo
select *
from table
where LENGTH(name) - LENGTH(REPLACE(name, 'a', '')) between 1 and 2
Updated to use between.
更新以在两者之间使用。
回答by Xophmeister
I don't know what MySQL supports in terms of lookaround assertions, but the following will do the trick:
我不知道 MySQL 在环视断言方面支持什么，但以下内容可以解决问题：
^(?=.*a.*a?.*)(?!.*a.*a.*a.*).*$
We have a lookahead assertion that matches 1 or 2 acharacters in the string. Then we have a negative lookahead that disregards 3 or more as anywhere in the string. Then the final pattern just matches the whole string, providing the first two assertions are satisfied.
我们有一个前瞻断言匹配a字符串中的 1 或 2 个字符。然后我们有一个否定前瞻，忽略a字符串中任何地方的3 个或更多s。然后最后的模式只匹配整个字符串，前提是前两个断言得到满足。
If MySQL doesn't support lookarounds, then @Woot4Moo's answer would be the way to go.
如果 MySQL 不支持环视，那么@Woot4Moo 的答案就是要走的路。
回答by Art
Quick and dirty:
又快又脏：
Select word, number_of_as From
(
Select 'akkka' word, REGEXP_COUNT('akkka', 'a') number_of_as From dual
)
Where number_of_as <= 2
/
