SQL Server provides the Contains clause for performing a variety of text searches. Using the Contains clause
provides search results and the rank of the result based on the query.
To look for a word in a table by searching every column:
SELECT * FROM TABLE1 WHERE CONTAINS(*, 'word')
To look for a wildcard, use * as follows:
SELECT * FROM TABLE1 WHERE CONTAINS(*, '"word*"')
To find a word near another word, use NEAR:
SELECT * FROM TABLE1 WHERE CONTAINS(*, 'word' NEAR 'another')
To get verb changes, use FORMSOF and INFLECTIONAL. For example, to search on waste and get
wasting, use:
SELECT * FROM TABLE1 WHERE CONTAINS(*, 'FORMSOF(INFLECTIONAL, waste)')
To get hits above a certain weight--say, .7--use:
SELECT * FROM TABLE1 WHERE CONTAINS(*,'word' NEAR 'another' WEIGHT(.7))
As you can see, this is a very powerful clause to consider when performing text searches. It should be noted,
however, that the clause is not transportable to other databases.
SELECT Object_Name(SysObjects.ID) TableName, Rows, DPages
FROM SysIndexes, SysObjects
WHERE SysIndexes.ID = SysObjects.ID AND
SysObjects.Type = 'U' AND
SysIndexes.IndID IN (0, 1)
ORDER BY Rows DESC
