获取字符串在列中出现次数的计数的SQL函数?

一个显而易见但不可分割的方法是这样的

(LENGTH(`my_column`) - LENGTH(REPLACE(`my_column`, 'my word', '')))/LENGTH('my word')

你调查过吗 Full Text 在MySQL中搜索?

取决于您所说的“字符串发生”是什么意思——作为子字符串还是作为整数值?

全值案例:

SELECT COUNT(*) FROM my_table WHERE my_column = 'my word';

子串情况:

SELECT COUNT(*) FROM my_table WHERE my_column LIKE '%my word%';

我想你可以用下面的例子。我试图计算一个特定的纸箱类型在运输时使用的次数。

SELECT carton_type, COUNT(carton_type) AS match_count
FROM carton_hdr
WHERE whse ='wh1'
GROUP BY "carton_type"

你的场景:

SELECT my_column COUNT(my_column)
FROM my_table
WHERE my_column = 'my_word'
GROUP BY my_column

如果您取出“where”函数,它将计算每个不同条目出现在“my_column”中的次数。

我只需要做一些类似的事情,但采取了不同的方法。我将相关的字符串复制到一个临时表中,在该表中,我可以添加列来跟踪每一行中出现的每个索引。

在我的示例中,我在产品描述中查找子字符串“-”(空格-破折号空格),目的是最终将这些子字符串切碎以显示为项目符号点,然后我分析类似这样的数据,以了解产品通常有多少个“项目符号”。

我怀疑这比重复地重写字符串值更有效,但实际上我还没有进行基准测试。

SELECT 
        ccp.ProductID, p.ProductDescription, descrlen=LEN(p.ProductDescription), 
        bulletcnt=0, indx=0, lastmatchat=0
    INTO #DescrBullets
    FROM Private.CompositeCatalogProduct AS ccp WITH(NOLOCK) 
    INNER JOIN Products.Product AS p WITH(NOLOCK) ON p.ProductId = ccp.ProductID
    WHERE ccp.CompositeCatalogID=53


DECLARE @rows INT = 1
WHILE @rows>0
BEGIN 

-- find the next occurrence on each row that's still in play
UPDATE #DescrBullets
    SET lastmatchat = PATINDEX('% - %',RIGHT(ProductDescription,descrlen-indx))
    WHERE indx<descrlen

-- anywhere that a match was found, increment my counter, and move my
-- index "cursor" past it
UPDATE #DescrBullets
    SET bulletcnt = bulletcnt + 1,
        indx = indx + lastmatchat + 2
    WHERE lastmatchat>0
SET @rows = @@ROWCOUNT

-- for all the ones that didn't have a match, advance indx past the end
-- so we don't need to reprocess on next iterations
UPDATE #DescrBullets
    SET indx=descrlen
    WHERE lastmatchat=0

RAISERROR('processing, %d products still have bullets', 0, 1, @rows) WITH NOWAIT

END 

SELECT db.bulletcnt, occurs=COUNT(*)
    FROM #DescrBullets AS db
    GROUP BY db.bulletcnt
    ORDER BY 1