Full text Search
-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 5 - Using Full-Text Search
--
-- Paul Nielsen
-----------------------------------------------------------
-----------------------------------------------------------
-- Creating a Full-Text Index
USE AESOP
-- SQL Where Like
SELECT Title
FROM Fable
WHERE Fabletext LIKE '%lion%'
AND Fabletext LIKE '%bold%'
CREATE FULLTEXT CATALOG AesopFT
CREATE FULLTEXT INDEX ON dbo.Fable(Title, Moral, Fabletext)
KEY INDEX FablePK ON AesopFT
WITH CHANGE_TRACKING AUTO
GO
-----------------------------------------------------
-- Searching for Words
USE AESOP
-- Contains
SELECT Title
FROM Fable
WHERE CONTAINS (Fable.*,'Lion')
-- Joining with Full-Text Search
SELECT *
FROM CONTAINSTABLE (Fable, *, 'Lion')
SELECT Fable.Title, Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, *, 'Lion') FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY FTS.Rank DESC
SELECT Fable.Title, Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, *, 'Lion', 2) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY FTS.Rank
-- Advanced Options
-- Phrases
SELECT Title
FROM Fable
WHERE CONTAINS (*,' "Wolf! Wolf!" ')
-- Or, And
SELECT Title
FROM Fable
WHERE CONTAINS (FableText,'Tortoise AND Hare')
SELECT Title
FROM Fable
WHERE CONTAINS (*,' "Thrifty AND supperless" ')
SELECT Title
FROM Fable
WHERE CONTAINS (*,'Thrifty')
AND CONTAINS(*,'supperless')
-- Wildcards *
SELECT Title
FROM Fable
WHERE CONTAINS (*,' "Hunt*" ')
-- Near
SELECT Title
FROM Fable
WHERE CONTAINS (*,'pardoned NEAR forest')
SELECT Title
FROM Fable
WHERE CONTAINS (*,'lion NEAR paw NEAR bleeding')
SELECT Fable.Title, Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, *,'life NEAR death') FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY FTS.Rank DESC
-- Weighting
SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, fabletext, 'ISABOUT (Lion weight (.5), Brave weight (.5), Eagle weight (.5))',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC
SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, fabletext, 'ISABOUT (Lion weight (.2), Brave weight (.2), Eagle weight (.8))',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC
SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, *, 'ISABOUT (Lion weight (.2), Brave weight (.2), Eagle weight (.8))',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC
-- Inflection
SELECT Title
FROM Fable
WHERE CONTAINS (*,'FORMSOF(INFLECTIONAL,pity)')
SELECT Title
FROM Fable
WHERE CONTAINS (*,'FORMSOF(INFLECTIONAL,geese)')
SELECT Title
FROM Fable
WHERE CONTAINS (*,'FORMSOF(INFLECTIONAL,carry)')
SELECT Title
FROM Fable
WHERE CONTAINS (*,'FORMSOF(INFLECTIONAL,fly)')
----------------------------------------------------
-- Using Fuzzy Searches
-- FreeText
SELECT Title
FROM Fable
WHERE FREETEXT (*,'The Tortoise beat the Hare in the big race')
SELECT Title
FROM Fable
WHERE FREETEXT (*,'The eagle was shot by an arrow')
SELECT Title
FROM Fable
WHERE FREETEXT (*,'The brave hunter kills the lion')
-- FreeText Table
SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN FREETEXTTABLE (Fable, *, 'The brave hunter kills the lion',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC
SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN FREETEXTTABLE (Fable, *, 'The eagle was shot by an arrow',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC
0 Response to "Full text Search"
Post a Comment