SQL function to get count of how many times string appears in column?

Question

Is there a function for MySQL that will count the number of times a string occurs in another string or column? Basically I want:

SELECT
    SUB_COUNT('my word', `my_column`) AS `match_count`
FROM `table`

Thanks!

EDIT: I need to know how many times the string appears in a column for each row in a SELECT.

Do you want to count the records where the string occurs in a field, or do you want to count how many times the string occurs in a field in each record? — Guffa, Jun 13 '10 at 08:37

score 17 · Accepted Answer · answered Jun 13 '10 at 08:39

17

An obvious but unscalable way is like this

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

Have you investigated Full Text search in MySQL?

answered Jun 13 '10 at 08:39

Martin Smith

438,706
87
741
845

I am using Full Text Search w/ MySQL, but I'm doing some relevancy ordering. Your solution is great, as I can avoid the divide when just ordering. – Kristopher Ives Jun 13 '10 at 09:00
This works without the division. Just do the math. LENGTH(`my_column`) - LENGTH(REPLACE(`my_column`, 'my word', '')) – givonz Mar 21 '22 at 14:25
1

@givonz the division is required. If the string is "Tora! Tora! Tora!" With length 17 chars and then replace all Tora with empty string the length of the remaining string is 5 chars. Subtracting one by the other gives 12. But "Tora" only appears in the string three times, not twelve times – Martin Smith Mar 21 '22 at 15:04
1

@MartinSmith I stand corrected. In my instance, it wasn't necessary. I was looking for a single character. – givonz Mar 22 '22 at 14:13

score 0 · Answer 2 · answered Jun 08 '15 at 16:39

I just needed to do something similar, but took a different approach. I copied the relevant string into a temp table where I can add columns to track an index through each occurrence on each row.

In my example, I'm looking for substrings " - " (space-dash-space) in product descriptions, with the intent of eventually chopping those apart to show as bullet points, and I'm analyzing the data like this to see how many "bullets" products typically have.

I suspect this is more efficient than repeatedly re-writing string values, but I haven't actually benchmarked.

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

score -1 · Answer 3 · edited Jan 22 '13 at 21:15

I think you may be able to use the following example. I was trying to count the number of times a particular carton type was used when shipping.

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

Your scenario:

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

If you take out the "where" function, it will count the number of times each distinct entry appears in "my_column".

SQL function to get count of how many times string appears in column?

3 Answers3

Linked