USE Northwind
-- This query returns 25 rows
SELECT * FROM products
WHERE CONTAINS(*, 'hot')
GO
-- This query returns rows that have both hot and spicy anywhere
-- in the row: 24 rows
SELECT * FROM products
WHERE CONTAINS(*, 'hot and spicy')
-- This query returns rows that have hot but not spicy: 1 row
SELECT * FROM products
WHERE CONTAINS(*, 'hot and not spicy')
-- This query returns rows that contain any words that start with
-- "ch" Note that the double quotes are needed; otherwise,
-- the "*" will not be interpreted as a wildcard. The result set is
-- 9 rows, containing products that include "chocolate," "chowder,"
-- "chai," and "chinois."
SELECT * FROM products
WHERE CONTAINS(*, '"ch*"')
-- This query returns rows that contain the string “hot and spicy”;
-- note the double quotes inside the single quotes:
-- 15 rows
SELECT * FROM products
WHERE CONTAINS(*, '"hot and spicy"')
GO
-- The previous query really found all rows containing
-- 'not' followed by a noise word, followed by 'spicy'.
-- This query will find the same 15 rows:
SELECT * FROM products
WHERE CONTAINS(*, '"hot or spicy"')
-- The following query will not return any rows
-- because no rows have hot right next to spicy,
-- in that order:
SELECT * FROM products
WHERE CONTAINS(*, '"hot spicy"')
GO
-- This query will also return no rows.
-- Sweet is not a noise word, so it must actually appear
-- in the data, and it doesn’t,
-— at least not between hot and spicy.
SELECT * FROM products
WHERE CONTAINS(*, '"hot sweet spicy"')
GO
-- Find all rows containing any form of the word "season."
-- One row, with "seasoning," will be returned.
SELECT * FROM products
WHERE CONTAINS(*, 'FORMSOF (inflectional,season)' )
GO
-- This query returns rows that have hot near spicy
SELECT * FROM products
WHERE CONTAINS(*, 'hot near spicy')
GO
SELECT *
FROM Products
WHERE CONTAINS(*,'ISABOUT(spicy weight(.8), supreme weight(.2),
hot weight(.1))')
GO
SELECT *
FROM CONTAINSTABLE(Products, *,
'ISABOUT(spicy weight(.8), supreme weight(.1),hot weight(.2))')
GO
SELECT [KEY], RANK, ProductID, ProductName
FROM CONTAINSTABLE(Products, *,'
ISABOUT(spicy weight(.8), supreme weight(.1), hot weight(.2))') C
JOIN Products P
ON P.productID = C.[KEY]
ORDER BY RANK DESC
GO
SELECT [KEY], RANK, ProductID, ProductName
FROM CONTAINSTABLE(Products, *,'hot or tofu ') C
JOIN Products P
ON P.productID = C.[KEY]
ORDER BY RANK DESC
GO
SELECT *
FROM Products
WHERE FREETEXT (*, 'I love hot and spicy scones')
GO
SELECT *
FROM Products
WHERE FREETEXT (*, 'Highly seasoned foods')
GO
SELECT [Key], Rank, ProductID, ProductName
FROM FREETEXTTABLE (Products, *,'I love hot and spicy scones') F
JOIN Products P
ON P.productID = F.[KEY]
ORDER BY RANK DESC
GO
SELECT [KEY], RANK, ProductID, ProductName
FROM CONTAINSTABLE(Products, *,'
ISABOUT(spicy weight(.8), supreme weight(.1), hot weight(.2))', 10) C
JOIN Products P
ON P.productID = C.[KEY]
ORDER BY RANK DESC
GO
SELECT [KEY], RANK, ProductID, ProductName
FROM CONTAINSTABLE(Products, *,'
ISABOUT(spicy weight(.8), supreme weight(.1), hot weight(.2))', 10) C
JOIN Products P
ON P.productID = c.[KEY]
WHERE categoryID = 6
ORDER BY RANK DESC
GO
SELECT TOP 10 [KEY], RANK, ProductID, ProductName
FROM CONTAINSTABLE(Products, *,'
ISABOUT(spicy weight(.8), supreme weight(.1), hot weight(.2))') C
JOIN Products P
ON P.productID = C.[KEY]
WHERE categoryID = 6
ORDER BY RANK DESC
GO
