The $CONTAINS function expands the search capabilities of SQL to include more flexible textual searches.
Most SQL statements are written with criteria in simple predicates, such as: FIRST_NAME = ‘William’. This predicate compares the entire contents of FIRST_NAME against the criteria ‘William’, providing no opportunities for more flexible searches. If the criterion does not match the column exactly, then the row will not be included. Given the likelihood of everything from nicknames to misspellings, this means that a lot of rows are “lost”, never to be seen by the user despite their importance and relevance.
SQL does provide a LIKE operator that allows exact substring searches and pattern-match searches. But this is very limited, and does not accommodate nicknames and misspellings. Furthermore, the LIKE operator has no awareness of the parsed words in a field. LIKE’s substring searches would find the word “LACE” in “WALLACE”, and the word “OLD” in “REYNOLDS” despite their lack of relevance.
There is no standardized SQL to support textual searches involving keyword searches, proximity searches, synonym searches, spellchecking and similar features. Some database vendors have implemented their own independent approaches, but they vary between databases.
This function returns a BOOLEAN datatype and can only be referenced in the WHERE clause of a SELECT statement.
See also: