MySQL Forums
Forum List  »  Full-Text Search

Re: Can I count the word in each row, when have some word occur many times
Posted by: Jay Alverson
Date: April 08, 2009 01:27PM

You can do something like this. Thanks to Laptop Alias.

mysql> 
mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.67-community-nt | 
+---------------------+
1 row in set (0.00 sec)

mysql> 
mysql> use test;
Database changed
mysql> 
mysql> drop table if exists quotes;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> CREATE TABLE quotes (quote CHAR(100),FULLTEXT (quote));
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> INSERT INTO quotes VALUES
    -> ('Special times require special socks'),
    -> ('Knock three times on the ceiling'),
    -> ('Boliauns are weeds'),
    -> ('The leprechaun''s gold');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT COUNT(*) from quotes WHERE MATCH(quote) AGAINST('special');
+----------+
| COUNT(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)

mysql> 
mysql> set @chars:=length("special");
Query OK, 0 rows affected (0.00 sec)

mysql> select @chars;
+--------+
| @chars |
+--------+
| 7      | 
+--------+
1 row in set (0.00 sec)

mysql> 
mysql> select hm as "How Many Specials" from (
    -> select
    -> (@chars:=length("special")) as "Word Length",
    -> (@line:=length(quote)) as "Full Text",
    -> (@none:=length(replace(lcase(quote), "special", ""))) as "Without the Word",
    -> (@diff:=@line-@none) as "The Difference",
    -> (@howmany:=round(@diff/@chars)) as hm
    -> from quotes) as Scratchtable;
+-------------------+
| How Many Specials |
+-------------------+
|                 2 | 
|                 0 | 
|                 0 | 
|                 0 | 
+-------------------+
4 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> notee

>

Thanks, Jay

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Can I count the word in each row, when have some word occur many times
4493
April 08, 2009 01:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.