Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Count the number of occurrences of a string in a VARCHAR field?

Tags:

sql

mysql

People also ask

How do you count a string occurrence in a string?

First, we split the string by spaces in a. Then, take a variable count = 0 and in every true condition we increment the count by 1. Now run a loop at 0 to length of string and check if our string is equal to the word.

How do I count the number of occurrences of a char in a string?

The string count() method returns the number of occurrences of a substring in the given string. In simple words, count() method searches the substring in the given string and returns how many times the substring is present in it.

How do I count the number of repeated characters in a column in SQL?

Try this: SELECT COUNT(DECODE(SUBSTR(UPPER(:main_string),rownum,LENGTH(:search_char)),UPPER(:search_char),1)) search_char_count FROM DUAL connect by rownum <= length(:main_string); It determines the number of single character occurrences as well as the sub-string occurrences in main string.


This should do the trick:

SELECT 
    title,
    description,    
    ROUND (   
        (
            LENGTH(description)
            - LENGTH( REPLACE ( description, "value", "") ) 
        ) / LENGTH("value")        
    ) AS count    
FROM <table> 

A little bit simpler and more effective variation of @yannis solution:

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.

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> 

try this:

 select TITLE,
        (length(DESCRIPTION )-length(replace(DESCRIPTION ,'value','')))/5 as COUNT 
  FROM <table> 


SQL Fiddle Demo


In SQL SERVER, this is the answer

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 LENGTH while REPLACE is same.

So the equivalent query in MySql should be

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.


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;

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)) );


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.


Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!