SQL Contains - only match at start

Question

For some reason I cannot find the answer on Google! But with the SQL contains function how can I tell it to start at the beginning of a string, I.e I am looking for the full-text equivalent to LIKE 'some_term%'.

I know I can use like, but since I already have the full-text index set up, AND the table is expected to have thousands of rows, I would prefer to use Contains.

Thanks!

Answer 1

You want something like this:

Rather than specify multiple terms, you can use a 'prefix term' if the terms begin with the same characters. To use a prefix term, specify the beginning characters, then add an asterisk (*) wildcard to the end of the term. Enclose the prefix term in double quotes. The following statement returns the same results as the previous one.

-- Search for all terms that begin with 'storm'
SELECT StormID, StormHead, StormBody FROM StormyWeather
WHERE CONTAINS(StormHead, '"storm*"')

http://www.simple-talk.com/sql/learn-sql-server/full-text-indexing-workbench/

Answer 2

You can use CONTAINS with a LIKE subquery for matching only a start:

SELECT * 
FROM (
    SELECT * 
    FROM myTable WHERE CONTAINS('"Alice in wonderland"')
) AS S1 
WHERE S1.edition LIKE 'Alice in wonderland%' 

This way, the slow LIKE query will be run against a smaller set