Wildcard search in SQL Server Full Text

Most people, me included, didn't think it was possible to do a wildcard search with full text in SQL Server. Well you can and whats more you can do it in 2000 as well as 2005.

Okay so you can't do a full wildcard search i.e. "s*n s*b*n" to find Simon Sabin, you can only have your wildcard at the end i.e. "sim*" to find Simon, Simone, Simple, you get the picture.

The trick is to enclose you search in quotes that way the full text engine knows to do a prefix search. I believe you can do a search with multiple prefix searches so "simo* sabi*" would return Simon Sabin.

This is a great feature if you put meta data in you index. This can be done to enhance the search optins and also enhance performance of full text, by restricting the rows that are returned. For example you want to be able to do some processing on data that is added to your full text index since the last time a process ran, but you are only concerned with the data thats been added.

If you add a metadata token into your search text that represents the date and time, something time DATE200511071125 to represent 7 Nov 2005 12:25 you can then do a prefix search "DATE2005110711*" to find all data added to the index between 11 and 12 on the 7 Nov 2005. Whats more if you want everything betwen 9 and 12 just OR your searches,  "DATE2005110709*"  OR "DATE2005110710*"  OR "DATE2005110711*".

Although you may not think so this is very performant, the reason is that the time consuming part of full text (when involving large result sets) is the returning of the data to the Sql Server Engine, therefore if your search adds a litle bit more time in building the result set in the full text search engine but a smaller resultset is returned to Sql Server you should find a performance improvement.  

Published Monday, November 7, 2005 3:44 AM by simonsabin
Filed under:


Tuesday, November 20, 2007 9:04 AM by Kate

# re: Wildcard search in SQL Server Full Text

Hi Simon,

Is it possible to use variables at the file section of collection tab at the foreach loop editor? The variable contains an expression that should be evaluated first. What should I do? Should I put the expression directly in the file text box? The variable is using DATEADD, GETDATE etc. functions.