MySQL 计算字符串在 VARCHAR 字段中出现的次数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12344795/
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 the number of occurrences of a string in a VARCHAR field?
提问by Geekman
I have a table like this:
我有一张这样的表：
TITLE | DESCRIPTION
------------------------------------------------
test1 | value blah blah value
test2 | value test
test3 | test test test
test4 | valuevaluevaluevaluevalue
I am trying to figure out how to return the number of times a string occurs in each of the DESCRIPTION's.
我想弄清楚如何返回字符串在每个描述中出现的次数。
So, if I want to count the number of times 'value' appears, the sql statement will return this:
所以，如果我想计算 'value' 出现的次数，sql 语句将返回：
TITLE | DESCRIPTION | COUNT
------------------------------------------------------------
test1 | value blah blah value | 2
test2 | value test | 1
test3 | test test test | 0
test4 | valuevaluevaluevaluevalue | 5
Is there any way to do this? I do not want to use php at all, just mysql.
有没有办法做到这一点？我根本不想使用php，只想使用mysql。
回答by yannis
This should do the trick:
这应该可以解决问题：
SELECT
title,
description,
ROUND (
(
LENGTH(description)
- LENGTH( REPLACE ( description, "value", "") )
) / LENGTH("value")
) AS count
FROM <table>
回答by gaborsch
A little bit simpler and more effective variation of @yannis solution:
@yannis 解决方案的更简单和更有效的变体：
SELECT
title,
description,
CHAR_LENGTH(description) - CHAR_LENGTH( REPLACE ( description, 'value', '1234') )
AS `count`
FROM <table>
The difference is that I replace the "value" string with a 1-char shorter string ("1234" in this case). This way you don't need to divide and round to get an integer value.
不同之处在于我用 1 个字符的较短字符串（在本例中为“1234”）替换了“value”字符串。这样你就不需要除以四舍五入来得到一个整数值。
Generalized version (works for every needle string):
通用版本（适用于每个针线）：
SET @needle = 'value';
SELECT
description,
CHAR_LENGTH(description) - CHAR_LENGTH(REPLACE(description, @needle, SPACE(LENGTH(@needle)-1)))
AS `count`
FROM <table>
回答by Joe G Joseph
try this:
尝试这个：
select TITLE,
(length(DESCRIPTION )-length(replace(DESCRIPTION ,'value','')))/5 as COUNT
FROM <table>
SQL Fiddle Demo
SQL 小提琴演示
回答by Niladri Biswas
In SQL SERVER, this is the answer
在 SQL SERVER 中，这就是答案
Declare @t table(TITLE VARCHAR(100), DESCRIPTION VARCHAR(100))
INSERT INTO @t SELECT 'test1', 'value blah blah value'
INSERT INTO @t SELECT 'test2','value test'
INSERT INTO @t SELECT 'test3','test test test'
INSERT INTO @t SELECT 'test4','valuevaluevaluevaluevalue'
SELECT TITLE,DESCRIPTION,Count = (LEN(DESCRIPTION) - LEN(REPLACE(DESCRIPTION, 'value', '')))/LEN('value')
FROM @t
Result
结果
TITLE DESCRIPTION Count
test1 value blah blah value 2
test2 value test 1
test3 test test test 0
test4 valuevaluevaluevaluevalue 5
I don't have MySQL install, but goggled to find the Equivalent of LEN is LENGTHwhile REPLACEis same.
我没有安装 MySQL，但目瞪口呆地发现 LEN 的等效项是LENGTH，而REPLACE是相同的。
So the equivalent query in MySql should be
所以MySql中的等价查询应该是
SELECT TITLE,DESCRIPTION, (LENGTH(DESCRIPTION) - LENGTH(REPLACE(DESCRIPTION, 'value', '')))/LENGTH('value') AS Count
FROM <yourTable>
Please let me know if it worked for you in MySql also.
请让我知道它是否也适用于 MySql。
回答by michaelbn
Here is a function that will do that.
这是一个可以做到这一点的函数。
CREATE FUNCTION count_str(haystack TEXT, needle VARCHAR(32))
RETURNS INTEGER DETERMINISTIC
BEGIN
RETURN ROUND((CHAR_LENGTH(haystack) - CHAR_LENGTH(REPLACE(haystack, needle, ""))) / CHAR_LENGTH(needle));
END;
回答by jfx
This is the mysql function using the space technique (tested with mysql 5.0 + 5.5):
CREATE FUNCTION count_str( haystack TEXT, needle VARCHAR(32))
RETURNS INTEGER DETERMINISTIC
RETURN LENGTH(haystack) - LENGTH( REPLACE ( haystack, needle, space(char_length(needle)-1)) );
这是使用空格技术的mysql函数（用mysql 5.0 + 5.5测试）：
CREATE FUNCTION count_str( haystack TEXT, needle VARCHAR(32))
RETURNS INTEGER DETERMINISTIC
RETURN LENGTH(haystack) - LENGTH( REPLACE ( haystack, needle, space(char_length(needle)-1)) );
回答by Trimantra Software Solution
SELECT
id,
jsondata,
ROUND (
(
LENGTH(jsondata)
- LENGTH( REPLACE ( jsondata, "sonal", "") )
) / LENGTH("sonal")
)
+
ROUND (
(
LENGTH(jsondata)
- LENGTH( REPLACE ( jsondata, "khunt", "") )
) / LENGTH("khunt")
)
AS count1 FROM test ORDER BY count1 DESC LIMIT 0, 2
Thanks Yannis, your solution worked for me and here I'm sharing same solution for multiple keywords with order and limit.
谢谢 Yannis，您的解决方案对我有用，我在这里为多个带有订单和限制的关键字共享相同的解决方案。
