Home » Questions » Computers [ Ask a new question ]

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

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

"Note: I am using SQL's Full-text search capabilities, CONTAINS clauses and all - the * is the wildcard in full-text, % is for LIKE clauses only.

I've read in several places now that ""leading wildcard"" searches (e.g. using ""*overflow"" to match ""stackoverflow"") is not supported in MS SQL. I'm considering using a CLR function to add regex matching, but I'm curious to see what other solutions people might have.

More Info: You can add the asterisk only at the end of the word or phrase. - along with my empirical experience: When matching ""myvalue"", ""my*"" works, but ""(asterisk)value"" returns no match, when doing a query as simple as:

SELECT * FROM TABLENAME WHERE CONTAINS(TextColumn, '""*searchterm""');

Thus, my need for a workaround. I'm only using search in my site on an actual search page - so it needs to work basically the same way that Google works (in the eyes on a Joe Sixpack-type user). Not nearly as complicated, but this sort of match really shouldn't fail."

Asked by: Guest | Views: 74
Total answers/comments: 3
Guest [Entry]

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

"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""')"
Guest [Entry]

"To perhaps add clarity to this thread, from my testing on 2008 R2, Franjo is correct above. When dealing with full text searching, at least when using the CONTAINS phrase, you cannot use a leading , only a trailing functionally. * is the wildcard, not % in full text.

Some have suggested that * is ignored. That does not seem to be the case, my results seem to show that the trailing * functionality does work. I think leading * are ignored by the engine.

My added problem however is that the same query, with a trailing *, that uses full text with wildcards worked relatively fast on 2005(20 seconds), and slowed to 12 minutes after migrating the db to 2008 R2. It seems at least one other user had similar results and he started a forum post which I added to... FREETEXT works fast still, but something ""seems"" to have changed with the way 2008 processes trailing * in CONTAINS. They give all sorts of warnings in the Upgrade Advisor that they ""improved"" FULL TEXT so your code may break, but unfortunately they do not give you any specific warnings about certain deprecated code etc. ...just a disclaimer that they changed it, use at your own risk.


Maybe, this is the closest MS hit related to these issues... http://msdn.microsoft.com/en-us/library/ms143709.aspx"