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

Question

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

User · Answer

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>

User · Answer

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

User · Answer

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  lt yourTable gt    Please let me know if it worked for you in MySql also

User · Answer

This should do the trick  SELECT      title      description          ROUND                            LENGTH description              - LENGTH  REPLACE   description   quot value quot    quot  quot                  LENGTH  quot value quot                 AS count     FROM  lt table gt

User · Answer

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

User · Answer

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

User · Answer

try this    select TITLE           length DESCRIPTION  -length replace DESCRIPTION   value        5 as COUNT    FROM  lt table gt       SQL Fiddle Demo

[mysql] Count the number of occurrences of a string in a VARCHAR field?

Examples related to mysql

Examples related to sql