Full-Text Search: Prefix / Suffix Search

Please help me to create an SQL Server 2000 Stored Procedure for using prefix and suffix terms.

Example:

Say I want to find "Terminator" (1984).

I want to be able to use "Term" or "ator" as search results and still return the proper record.

Here is my Stored Procedure creation sql:

CREATE PROCEDURE sps_searchTitles(@.searchTerm varchar(255)) AS
SELECT * FROM Video
WHERE FREETEXT (Video.*, '"*@.searchTerm*"')
GO

-- The above does not appear to properly check both prefix ("Term--") and suffix ("--ator") terms.

I am trying to accomplish what is similarly done with LIKE '%term%'.

thanks, YMYou want to use CONTAINS, not FREETEXT. For example:

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "choc*" ')
GO