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.
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.
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>
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With