matchAll
Syntax
matchAll(textCol, terms)
Arguments
textColis the column to be searched, i.e., the column with text indexing set in the PKEY engine.
terms is a STRING scalar specifying the term(s) to search for. To search for multiple terms, separate them with spaces.
Details
Perform word-based text searches 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 all of the specified terms.
Examples
// 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 "apple" and "orange"
select * from pt where matchAll(textCol=remark,terms="apple orange")
id1 | id2 | remark |
---|---|---|
1 | 1 | There are some apples and oranges. |
3 | 3 | Mike traded an orange for an apple with Alice. |