How do you get leading wildcard full-text searches to work in SQL Server?

Workaround only for leading wildcard:

• store the text reversed in a different field (or in materialised view)
• create a full text index on this column

• find the reversed text with an *

  SELECT * 
  FROM TABLENAME 
  WHERE CONTAINS(TextColumnREV, '"mrethcraes*"');
  

Of course there are many drawbacks, just for quick workaround...

Not to mention CONTAINSTABLE...

The problem with leading Wildcards: They cannot be indexed, hence you're doing a full table scan.

It is possible to use the wildcard "*" at the end of the word or phrase (prefix search).

For example, this query will find all "datab", "database", "databases" ...

SELECT * FROM SomeTable WHERE CONTAINS(ColumnName, '"datab*"')

But, unforutnately, it is not possible to search with leading wildcard.

For example, this query will not find "database"

SELECT * FROM SomeTable WHERE CONTAINS(ColumnName, '"*abase"')

Note: this was the answer I submitted for the original version #1 of the question before the CONTAINS keyword was introduced in revision #2. It's still factually accurate.

The wildcard character in SQL Server is the % sign and it works just fine, leading, trailing or otherwise.

That said, if you're going to be doing any kind of serious full text searching then I'd consider utilising the Full Text Index capabilities. Using % and _ wild cards will cause your database to take a serious performance hit.

One thing worth keeping in mind is that leading wildcard queries come at a significant performance premium, compared to other wildcard usages.

Just FYI, Google does not do any substring searches or truncation, right or left. They have a wildcard character * to find unknown words in a phrase, but not a word.

Google, along with most full-text search engines, sets up an inverted index based on the alphabetical order of words, with links to their source documents. Binary search is wicked fast, even for huge indexes. But it's really really hard to do a left-truncation in this case, because it loses the advantage of the index.

When it comes to full-text searching, for my money nothing beats Lucene. There is a .Net port available that is compatible with indexes created with the Java version.

There's a little work involved in that you have to create/maintain the indexes, but the search speed is fantastic and you can create all sorts of interesting queries. Even indexing speed is pretty good - we just completely rebuild our indexes once a day and don't worry about updating them.

As an example, this search functionality is powered by Lucene.Net.

Perhaps the following link will provide the final answer to this use of wildcards: Performing FTS Wildcard Searches.

Note the passage that states: "However, if you specify “Chain” or “Chain”, you will not get the expected result. The asterisk will be considered as a normal punctuation mark not a wildcard character. "

If you have access to the list of words of the full text search engine, you could do a 'like' search on this list and match the database with the words found, e.g. a table 'words' with following words:

    pie
    applepie
    spies
    cherrypie
    dog
    cat

To match all words containing 'pie' in this database on a fts table 'full_text' with field 'text':

    to-match <- SELECT word FROM words WHERE word LIKE '%pie%'
    matcher = ""
    a = ""
    foreach(m, to-match) {
      matcher += a
      matcher += m
      a = " OR "
    }
    SELECT text FROM full_text WHERE text MATCH matcher

% Matches any number of characters _ Matches a single character

I've never used Full-Text indexing but you can accomplish rather complex and fast search queries with simply using the build in T-SQL string functions.

From SQL Server Books Online:

To write full-text queries in Microsoft SQL Server 2005, you must learn how to use the CONTAINS and FREETEXT Transact-SQL predicates, and the CONTAINSTABLE and FREETEXTTABLE rowset-valued functions.

That means all of the queries written above with the % and _ are not valid full text queries.

Here is a sample of what a query looks like when calling the CONTAINSTABLE function.

SELECT RANK , * FROM TableName , CONTAINSTABLE (TableName, *, ' "*WildCard" ') searchTable WHERE [KEY] = TableName.pk ORDER BY searchTable.RANK DESC

In order for the CONTAINSTABLE function to know that I'm using a wildcard search, I have to wrap it in double quotes. I can use the wildcard character * at the beginning or ending. There are a lot of other things you can do when you're building the search string for the CONTAINSTABLE function. You can search for a word near another word, search for inflectional words (drive = drives, drove, driving, and driven), and search for synonym of another word (metal can have synonyms such as aluminum and steel).

I just created a table, put a full text index on the table and did a couple of test searches and didn't have a problem, so wildcard searching works as intended.

[Update]

I see that you've updated your question and know that you need to use one of the functions.

You can still search with the wildcard at the beginning, but if the word is not a full word following the wildcard, you have to add another wildcard at the end.

Example:  "*ildcar" will look for a single word as long as it ends with "ildcar".

Example:  "*ildcar*" will look for a single word with "ildcar" in the middle, which means it will match "wildcard".  [Just noticed that Markdown removed the wildcard characters from the beginning and ending of my quoted string here.]

[Update #2]

Dave Ward - Using a wildcard with one of the functions shouldn't be a huge perf hit. If I created a search string with just "*", it will not return all rows, in my test case, it returned 0 records.