Introduction to freetext and contains in SQL-Server

The CONTAINS predicate supports complex syntax for searching:

• One or more words using the logical AND/OR operators.
• word families
• A word or phrase beginning with a certain text.
• Words or phrases that are close to each other.

To search for a word in a field:

SELECT title_id, title, notes FROM titles

WHERE CONTAINS(notes,’business’)

To locate a phrase in a field:

SELECT title_id, titles, notes FROM titles

WHERE CONTAINS(notes,’ “common business applications” ‘)

To locate a phrase in all enabled fields:

SELECT title_id, titles, notes FROM titles

WHERE CONTAINS(*, ‘ “common business applications” ‘)

Using AND, OR and NOT

SELECT title, notes FROM titles

WHERE CONTAINS(notes, ‘ “favorite recipes” OR “gourmet recipes” ‘)

SELECT titles, notes FROM titles

WHERE CONTAINS(notes, ‘ cooking AND NOT (“computer*”) ‘)

SELECT titles, notes FROM titles

WHERE CONTAINS(notes, ‘ beer AND ales ‘)

SELECT titles, notes FROM titles

WHERE CONTAINS(*, ‘(“ice skating” OR hockey) AND NOT olympics’)

Using wildcards

SELECT titles, notes FROM titles

WHERE CONTAINS(notes,’ “ice*” ‘)

SELECT titles, notes FROM titles

WHERE CONTAINS(notes, ‘ “light bread*” ‘)

Search for words or phrases indicating the importance of the words:

This search allows you to indicate the weight that each of the searched words or phrases will have on the search result, the weight ranges between the lowest value 0.0 and the highest value 1.0.

SELECT Customer, Name, Address FROM Customer

WHERE CONTAINS (Address, ‘ISABOUT ( “Street*”, Velazquez WEIGHT(0.5), Serrano(0.9)’)

(All those records will be found that contain the string street followed by any value in the address field, ordering those of “Calle Serrano” first, then those of “Calle Velázquez” and then the rest.

Search for nearby words:

We can search for two words and indicate that they are close to each other. The order of the words does not alter the search result.

SELECT title, notes FROM books

WHERE CONTAINS (notes, “user NEAR computer”)

SELECT title, notes FROM books

WHERE CONTAINS (notes, “user ~ computer”)

Three words can be indicated, in such a way that the second and the first must be close as well as the second and the third.

SELECT title, notes FROM books

WHERE CONTAINS (notes, “user ~ beginner ~ computer”)

Phrase searches:

… WHERE CONTAINS(Description, ” sauces ~ “mix*” “)

… WHERE CONTAINS(Description, ” “meat*” ~ “patty sauce*” “)

The FREETEXT predicate

When using this predicate, all the words of the phrases are analyzed and it returns us as a result, those records that contain the complete phrase or some fragment of it. The syntax is the same as the CONTAINS predicate.

… FREETEXT(description, ‘ “In a place of the stain whose name I don’t want to remember” ‘)

The CONTAINSTABLE predicate

This predicate has the same function and syntax as CONTAINS, except that in this case it returns a table with two columns, the first call contains the value of the primary key of the table that we are looking for, the second RANK call returns a value indicating the percentage search hit for each record.

SELECT Questions.Question, Questions.Answer, Result.RANK

FROM Questions, CONTAINSTABLE(Response, ” SQL Server”) AS Result

WHERE Questions.IdQuestion = Result.

ORDER BY Result.RANK Desc

SELECT Questions.Question, Questions.Answer, Result.RANK

FROM Questions INNER JOIN CONTAINSTABLE (Response, “SQL Server”) AS Result

ON Questions.IdQuestion = Results.KEY

The FREETEXTTABLE predicate

It is the equivalent of CONTAINSTABLE but performing FREETEXT searches.