计算VARCHAR字段中字符串出现的次数？

Question

计算VARCHAR字段中字符串出现的次数？

我有一张这样的桌子：

TITLE          |   DESCRIPTION

------------------------------------------------

test1          |   value blah blah value

test2          |   value test

test3          |   test test test

test4          |   valuevaluevaluevaluevalue

我试图弄清楚如何返回每个描述中字符串发生的次数。

因此，如果我想计算出现‘value’的次数，SQL语句将返回以下内容：

TITLE          |   DESCRIPTION                  |   COUNT

------------------------------------------------------------

test1          |   value blah blah value        |   2

test2          |   value test                   |   1

test3          |   test test test               |   0

test4          |   valuevaluevaluevaluevalue    |   5

有什么办法吗？我根本不想使用php，只想使用MySQL。

Answer 1

这应该能起作用：SELECT&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;title, &nbsp;&nbsp;&nbsp;&nbsp;description,&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;ROUND&nbsp;(&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LENGTH(description) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&nbsp;LENGTH(&nbsp;REPLACE&nbsp;(&nbsp;description,&nbsp;"value",&nbsp;"")&nbsp;)&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;/&nbsp;LENGTH("value")&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;AS&nbsp;count&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp;<table>

Answer 2

试试这个：&nbsp;select&nbsp;TITLE, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(length(DESCRIPTION&nbsp;)-length(replace(DESCRIPTION&nbsp;,'value','')))/5&nbsp;as&nbsp;COUNT&nbsp; &nbsp;&nbsp;FROM&nbsp;<table>SQL Fiddle演示

Answer 3

请让我知道它是否对你的MySQL也有效。在sql server中，这是答案。Declare @t table(TITLE VARCHAR(100), DESCRIPTION VARCHAR(100))INSERT INTO @t SELECT 'test1', 'value blah blah value'&nbsp;INSERT INTO @t SELECT 'test2','value test'&nbsp;INSERT INTO @t SELECT 'test3','test test test'&nbsp;INSERT INTO @t SELECT 'test4','valuevaluevaluevaluevalue'&nbsp;SELECT TITLE,DESCRIPTION,Count = (LEN(DESCRIPTION) - LEN(REPLACE(DESCRIPTION, 'value', '')))/LEN('value')&nbsp;FROM @t结果TITLE&nbsp; &nbsp;DESCRIPTION&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Counttest1&nbsp; &nbsp;value blah blah value&nbsp; &nbsp; &nbsp; &nbsp; 2test2&nbsp; &nbsp;value test&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1test3&nbsp; &nbsp;test test test&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0test4&nbsp; &nbsp;valuevaluevaluevaluevalue&nbsp; &nbsp; 5我没有安装MySQL，但是我用眼镜来查找相当于Len的长度当取代是一样的。因此，MySQL中的等效查询应该是SELECT TITLE,DESCRIPTION, (LENGTH(DESCRIPTION) - LENGTH(REPLACE(DESCRIPTION, 'value', '')))/LENGTH('value') AS CountFROM <yourTable>请让我知道它是否对你的MySQL也有效。