PORTER English stemming tokenizer

/* ===================================================================== 
 * Full-text search demo using the PORTER tokenizer. 
 * ===================================================================== 
 */


/* Create the testsearch table. */
CREATE TABLE testsearch
( 
  name VARCHAR(30)
, description VARCHAR(500) 
) 
STORAGE_ATTRIBUTES 'hotAlter; huge; rowid_fld; noRecbyt_idx;'
;

/* Create full-text index on the description field of the testsearch table. Use 
the PORTER English word stem tokenizer to do case insensitive matching of 
common word forms */

CREATE FULLTEXT INDEX testsearch_description_porter_fts   
  ON  testsearch( description )  
  TOKENIZER    PORTER  
  UPDATE_MODE  ASYNC
;

/* Insert a record into the testsearch table. */
INSERT INTO testsearch 
VALUES('Mike','Americans   --love--  their __BASEball__ and <FOOTball>!')
;

/* Look at the record inserted into the testsearch table. */
SELECT  description FROM testsearch;
/* ===================================================================== 
 * The following queries find the inserted record 
 * by searching for words and phrases in the description field:
 * 'Americans   --love--  their __BASEball__ and <FOOTball>!'. 
 * 
 * The PORTER tokenizer reduces the text to 
 * 'american love their baseball and football'. 
 * 
 * The tokenizer identifies each word using whitespace and/or punctuation.
 * The tokenizer is case insensitive.
 * The tokenizer reduces each word to its stem 
 * by removing trailing s, er, ing, and ed.
 * The tokenizer finds all words matching the stem.
 * These words match each other -- even when they are invalid words: 
 * baseball baseballs baseballing baseballer baseballed
 * 
 * A search string identifies each word using whitespace and/or punctuation.
 * A search string may use double quotes to identify a word or a phrase.
 * A phrase contains two or more words within double quotes.
 * A search string may include any amount of whitespace between words.
 * A search string may include 
 * Punctuation, such as .!>, is ignored in the search string 
 * when it is not a full-text search operator. 
 * Reserved search operators include - " ^ * ( ) NEAR NEAR/n AND OR NOT
 * The following search operators are unsupported: * ( ) OR NOT
 * The server returns an error when you use an unsupported search operator.
 * =====================================================================
 */

/* WORD SEARCH: Find records in the testsearch table where the description 
column contains the word 'baseball' anywhere in the description field. */
SELECT description FROM testsearch WHERE description MATCH 'baseball';

/* VERB STEM SEARCH: Find records in the testsearch table where the description 
column contains common English conjugations of verbs, such as 'loving', 'love', 
'loved', and 'loves', anywhere in the description field. */
SELECT description FROM testsearch WHERE description MATCH 'loving';

/* NOUN STEM SEARCH: Find records in the testsearch table where the description 
column contains English singular and plural forms of nouns, such as 'american' 
and 'americans', anywhere in the description field. */
SELECT description FROM testsearch WHERE description MATCH 'american';

/* CASE INSENSITIVE SEARCH: Find records in the testsearch table where the 
description column contains common forms of the word 'baseBALLs', such as 
'baseball' and 'Baseballs' anywhere in the description field. */
SELECT description FROM testsearch WHERE description MATCH 'baseBALLs';

/* FIRST WORD SEARCH: Find records in the testsearch table where the 
description column contains the case-insensitive word 'americans' as the first 
word in the description field. */
SELECT description FROM testsearch WHERE description MATCH '^americans';

/* FIRST PHRASE SEARCH: Find records in the testsearch table where the 
description column contains the phrase "americans love" at the begining of the 
text in the description field. */
SELECT  description FROM testsearch WHERE description MATCH ' ^"americans love" 
';

/* MULTI-WORD SEARCH: Find records in the testsearch table where the 
description column contains the words 'baseball' and 'love' in any order 
anywhere in the description field. */
SELECT description FROM testsearch WHERE description MATCH 'baseball love';

/* MULTI-WORD SEARCH ACROSS WHITESPACE: Find records in the testsearch table 
where the description column contains the words 'baseball' and 'love' in any 
order anywhere in the description field while ignoring case and whitespace. */
SELECT description FROM testsearch WHERE description MATCH ' Baseball   >>>   
LOVE! ';

/* PHRASE SEARCH: Find records in the testsearch table where the description 
column contains the case-insensitive phrase "love their" anywhere in the 
description field. The words 'love" and 'their" must follow each other with no 
intervening words in the description field. */
SELECT description FROM testsearch WHERE description MATCH ' "love their" ';

/* PHRASE SEARCH ACROSS WHITESPACE: Find records in the testsearch table where 
the description column contains the case-insensitive phrase "LOVE their"  
anywhere in the description field. The case-insensitive words 'LOVE" and 
'theIR" must follow each other with no intervening words, but any amount of 
whitespace and punctuation may occur between these words in the description 
field. */
SELECT description FROM testsearch WHERE description MATCH ' "LOVE   theIR" ';

/* STEMMED MULTI-WORD SEARCH: Find records in the testsearch table where the 
description column contains several words anywhere in the description field, 
such as 'baseBALLs' and 'american'. The PORTER tokenizer matches each word that 
has the same English stemming. */
SELECT description FROM testsearch WHERE description MATCH 'Loved their 
american baseBALLs.';

/* STEMMED PHRASE SEARCH: Find records in the testsearch table where the 
description column contains common forms of each word in the phrase "and 
football" anywhere in the description field. */
SELECT description FROM testsearch WHERE description MATCH ' "and footballs" ';

/* STEMMED WORD SEARCH ACROSS INCORRECT WORD FORMS: Find records in the 
testsearch table where the description column contains correct and incorrect 
forms of words, such as 'baseballing', 'baseballer', 'baseballed'. The PORTER 
stemmer applies its rules to every word -- even when the rule is incorrect in 
English. */
SELECT description FROM testsearch WHERE description MATCH ' american americans 
americaning americaner americaned baseball baseballs baseballing baseballer 
baseballed their theirs theiring theired "and" "ands" "anding" "anded"  ';

/* STEMMED FIRST WORD SEARCH: Find records in the testsearch table where the 
description column contain common forms of the word 'americaning' as the first 
word in the description field. */
SELECT description FROM testsearch WHERE description MATCH '^americaning';

/* STEMMED FIRST PHRASE SEARCH: Find records in the testsearch table where the 
description column contains common forms of the words in the phrase "american 
loving" at the begining of the text in the description field. */
SELECT  description FROM testsearch WHERE description MATCH ' ^"american 
loving" ';

/* NEAR SEARCH: Find records in the testsearch table where the description 
column contains the words 'Americans' and 'baseball' with no more than ten 
words between each other anywhere in the description field. */
SELECT description FROM testsearch WHERE description MATCH 'Americans NEAR 
football';

/* NEAR PHRASE SEARCH: Find records in the testsearch table where the 
description column contains the words 'their' and 'baseballs' with no words 
between each other. This is the same as using a phrase search. */
SELECT description FROM testsearch WHERE description MATCH 'their NEAR/0 
baseball';

/* NEAR N SEARCH: Find records in the testsearch table where the description 
column contains the words 'love' and 'baseball' with no more than 1 word 
between each other. This pattern may occur anywhere in the description field 
with any amount of whitespace and punctuation between the words. */
SELECT description FROM testsearch WHERE description MATCH ' baseball NEAR/1 
football ';

/* NOT WORD SEARCH: Find records in the testsearch table where the description 
column contains the words 'love' and 'baseball' but not 'karate'. NOTE: The 
minus operator is the NOT operator. It must be adjacent to the word with no 
intervening whitespace. You must also include a term in the search phrase 
before the NOT operator. */
SELECT description FROM testsearch WHERE description MATCH 'love baseball 
-karate';

/* NOT PHRASE SEARCH: Find records in the testsearch table where the 
description column contains the word 'baseball' but not the phrase "hate 
karate" and not the word 'soccer'. NOTE: The minus operator is the NOT 
operator. It must be adjacent to the word with no intervening whitespace. You 
must also include a term in the search phrase before the NOT operator. */
SELECT description FROM testsearch WHERE description MATCH 'baseball -"hate 
karate" -soccer';

/* STEMMED NEAR SEARCH: Find records in the testsearch table where the 
description column contains case-insensitive common forms of the words 
'American' and 'baseball' with no more than ten words between each other 
anywhere in the description field. */
SELECT description FROM testsearch WHERE description MATCH 'americaner NEAR 
baseballs ';

/* STEMMED PHRASE SEARCH: Find records in the testsearch table where the 
description column contains case-insensitive common forms of the words 'their' 
and 'baseballs' with no words between each other anywhere in the description 
field. */
SELECT description FROM testsearch WHERE description MATCH 'their NEAR/0 
baseBALLS ';

/* STEMMED NEAR N SEARCH: Find records in the testsearch table where the 
description column contains case-insensitive common forms of the words 
'baseball' and 'football' with no more than 1 word between each other anywhere 
in the description field. */
SELECT description FROM testsearch WHERE description MATCH 'baseballing NEAR/1 
footballed ';

/* STEMMED NOT WORD SEARCH: Find records in the testsearch table where the 
description column contains case-insensitive common forms of the words 'love' 
and 'baseball' but not 'karate'. NOTE: The minus operator must be adjacent to 
the word or phrase with no intervening whitespace. You must also have a term in 
the search phrase before the minus - operator. */
SELECT description FROM testsearch WHERE description MATCH 'loves baseballing 
-karate';

/* STEMMED NOT PHRASE SEARCH: Find records in the testsearch table where the 
description column contains case-insensitive common forms of the word 
'baseball' but not the phrase "hate karate". */
SELECT description FROM testsearch WHERE description MATCH 'loving baseballs 
-"hating karate" ';

/* STEMMED NOT PHRASE SEARCH: Find records in the testsearch table where the 
description column contains case-insensitive common forms of the word 'love' 
but not case-insensitive common forms of the words in the phrase "Americans 
football". */
SELECT description FROM testsearch WHERE description MATCH 'Loved -"ameRICAN  
footBALLs" ';

/* Remove the testsearch table. */
DROP TABLE testsearch;