StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The way I would approach this is to write a little <a href="http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html" rel="nofollow">user defined function</a> to give me the number of times one string appears in another with some allowances for:</p> <ul> <li>upper and lower case </li> <li>common punctuation</li> </ul> <p>I would then create a table with all of the words that I wish to search with i.e. your 200 list. Then use the function to count the number of occurrences of each word in every phrase, put that in a inline view and then sum the results up by search word.</p> <p>Hence:</p> <p><strong>User Defined Function</strong></p> <pre><code>DELIMITER $$ CREATE FUNCTION `get_word_count`(phrase VARCHAR(500),word VARCHAR(255), delimiter VARCHAR(1)) RETURNS int(11) READS SQL DATA BEGIN DECLARE cur_position INT DEFAULT 1 ; DECLARE remainder TEXT; DECLARE cur_string VARCHAR(255); DECLARE delimiter_length TINYINT UNSIGNED; DECLARE total INT; DECLARE result DOUBLE DEFAULT 0; DECLARE string2 VARCHAR(255); SET remainder = replace(phrase,'!',' '); SET remainder = replace(remainder,'.',' '); SET remainder = replace(remainder,',',' '); SET remainder = replace(remainder,'?',' '); SET remainder = replace(remainder,':',' '); SET remainder = replace(remainder,'(',' '); SET remainder = lower(remainder); SET string2 = concat(delimiter,trim(word),delimiter); SET delimiter_length = CHAR_LENGTH(delimiter); SET cur_position = 1; WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO SET cur_position = INSTR(remainder, delimiter); IF cur_position = 0 THEN SET cur_string = remainder; ELSE SET cur_string = concat(delimiter,LEFT(remainder, cur_position - 1),delimiter); END IF; IF TRIM(cur_string) != '' THEN set result = result + (select instr(string2,cur_string) > 0); END IF; SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); END WHILE; RETURN result; END$$ DELIMITER ; </code></pre> <p>You might have to play with this function a little depending on what allowances you need to make for punctuation and case. Hopefully you get the idea here though!</p> <p><strong>Populate tables</strong></p> <pre><code>create table search_word (id int unsigned primary key auto_increment, word varchar(250) not null ); insert into search_word (word) values ('you'); insert into search_word (word) values ('how'); insert into search_word (word) values ('to'); insert into search_word (word) values ('too'); insert into search_word (word) values ('the'); insert into search_word (word) values ('and'); insert into search_word (word) values ('world'); insert into search_word (word) values ('hello'); create table phrase_to_search (id int unsigned primary key auto_increment, phrase varchar(500) not null ); insert into phrase_to_search (phrase) values ("How are you. It's been long since I met you"); insert into phrase_to_search (phrase) values ("I am fine how are you?"); insert into phrase_to_search (phrase) values ("Oh. Not bad. All is ok with the world, I think"); insert into phrase_to_search (phrase) values ("I think so too!"); insert into phrase_to_search (phrase) values ("You know what? I think so too!"); </code></pre> <p><strong>Run Query</strong></p> <pre><code>select word,sum(word_count) as total_word_count from ( select phrase,word,get_word_count(phrase,word," ") as word_count from search_word join phrase_to_search ) t group by word order by total_word_count desc; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload