matchPhraseInfix

matchPhraseInfix(textCol, suffix, phrase, prefix)

textCol is the column to be searched, i.e., the column with text indexing set in the PKEY engine.

phrase is a STRING scalar specifying the phrase to search for.

suffix is a STRING scalar specifying the suffix to search for.

prefix is a STRING scalar specifying the prefix to search for.

Perform text searches based on phrase, suffix, and prefix on the column with text indexing set in the PKEY engine. This function is used in the where clause of a SQL statement.

Return value: Rows containing the specified phrase that is followed by a word with the specified prefix and preceded by a word with the specified suffix.

// Generate data for queries
stringColumn = ["There are some apples and oranges.","Mike likes apples.","Alice likes oranges.","Mike gives Alice an apple.","Alice gives Mike an orange.","John likes peaches, so he does not give them to anyone.","Mike, can you give me some apples?","Alice, can you give me some oranges?","Mike traded an orange for an apple with Alice."]
t = table([1,1,1,2,2,2,3,3,3] as id1, [1,2,3,1,2,3,1,2,3] as id2, stringColumn as remark) 
if(existsDatabase("dfs://textDB")) dropDatabase("dfs://textDB")
db = database(directory="dfs://textDB", partitionType=VALUE, partitionScheme=[1,2,3], engine="PKEY")
pt = createPartitionedTable(dbHandle=db, table=t, tableName="pt", partitionColumns="id1",primaryKey=`id1`id2,indexes={"remark":"textindex(parser=english, lowercase=true, stem=true)"})
pt.tableInsert(t)

// Search for rows containing word "give" that is preceded by a word prefixed with "al" and followed by a word suffixed with "ke"
select * from pt where matchPhraseInfix(remark,"ke","give","al")