search word count query in mysql

Question

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.

Possible duplicate of http://stackoverflow.com/questions/748276/using-sql-to-determine-word-count-stats-of-a-text-field — Jacob Tomlinson, Apr 11 '13 at 08:11
I think `Jacob Tomlinson`'s reference is right. It might not be an exact duplicate but it points you in the right direction. `The "correct" way is to process the data outside the DB since DBs are for storage, not processing,...` — Ejaz, Apr 11 '13 at 08:34
@Ejay Thanx for help but i have got it..i have posted answer so other can solve same issue.. — Dipesh Parmar, Apr 11 '13 at 12:00
@JacobTomlinson thanx for being first to response and help me// — Dipesh Parmar, Apr 11 '13 at 12:00

Dipesh Parmar · Accepted Answer · 2013-04-11T12:13:51.297

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

search word count query in mysql

1 Answers1