Re: Can I count the word in each row, when have some word occur many times
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
Subject
Views
Written By
Posted
4990
April 08, 2009 12:29PM
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.