What now? Let’s make some queries!

I will be using in the following examples, queries with simple terms (One or more specific words or phrase). You can also check the references to see prefix terms, generation terms, proximity terms, thesaurus or weighted terms.

Given our Customers table:

Depending on your goal, it’s possible to extract the ranking score from the match.

Let’s search for the word ‘street’ in the column Address of the table Customers.

Without ranking

select *

from customers

where contains(customers.Address,'street')

It finds all Customers where the Address has a word with ‘street’ in it.

With Ranking Score

select customers.Name, customers.Address, key_search.rank

from customers

INNER JOIN CONTAINSTABLE(customers,(Address),’street’) AS key_search on customers.Id = key_search.[Key]

In this example I’am using the CONTAINSTABLE from Full-Text that looks at one column (or more) of one table and finds the word ‘ street’ .

This might be very helpful when having more than one word in the search and you want to sort by Rank score.

Now, if you want to search for the address that contains the word ‘street’ and it’s at the state ‘PA’. Then we need to break the string with AND’s or OR’s and order by ranking:

select customers.Name, customers.Address, key_search.rank as searchrank

FROM CUSTOMERS

INNER JOIN CONTAINSTABLE(customers,(address),'"street" and "PA"') AS key_search on customers.Id = key_search.[Key]

order by searchrank desc

Full-text queries use a small set of Transact-SQL predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE). You must use whatever fits you better.