CODEWITHSUNDEEP
phpmysqlword-count
Dipesh Parmar
Dipesh Parmar

Reputation: 27364

search word count query in mysql

I have below table.

CREATE TABLE IF NOT EXISTS `product`
(
    `id` int(11) NOT NULL,
    `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
    `description` varchar(200) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `product` (`id`, `name`, `description`) VALUES
(1, 'Samsung', "this is samsung product description from samsung"),
(2, 'Mobile', "Samsung galaxy note2 from samsung store"),
(3, 'Smartphone', "Samsung");

now i want to query on product table that produce result as below.

ID   Name       WordCount
1   Samsung        3
2   Mobile         2
3   Smartphone     1

how can i count this word occurrence in MySQL select query.

EDIT

Sorry for not clearing my point.

But here it is.

I want to search word Samsung from all the rows and count its occurrence not only from name but from also description too.

Upvotes: 1

Views: 1100

Answers (1)

Dipesh Parmar
Dipesh Parmar

Reputation: 27364

After few hours of googlling and debugging finally i have got it solved.

I have used combination of char_length and replace to achieve this task.

What i end up with is as below.

select  *,(
    (char_length(name) - char_length(replace(name,'sam',''))) +
    (char_length(description) - char_length(replace(description,'sam','')))
) / char_length('sam') as SearchCount
from
    product
order by
    SearchCount desc

above query is CASE SENSITIVE but do not worry i have also solved it with CASE-INSESITIVE see below query.

select  *,
(
    (char_length(name) - char_length(replace(LOWER(name),LOWER('Sam'),''))) +
    (char_length(description) - 
    char_length(replace(LOWER(description),LOWER('Sam'),'')))
) / char_length('sam') as SearchCount
from
    product
order by
    SearchCount desc

after having this query all we need to do is add WHERE clause to make it work.

Hope this will help other People too.

Thanks for help (All the people who answered and deleted and comment.)

Upvotes: 3

Related Questions