Solo Technology

New version of FogBugz came out recently and I got the latest build of it today. We use it at work to track bugs, projects and manage workflow for some of our internal form projects. The new build fixed some bugs, but the other folks who use it pointed out to me (for the billionth time) that searching for partial text remained broken. I can’t tell you how many times I’ve stalled them off by suggesting that we wait to see what the next version does…

I decided it was time to head to the support forums and do some griping — it had been a bit since the last time. Here’s my post:

[...] One of the things we use FB for is workflow management on form maintenance. As such, each form has a case. Each case’s title is the form name. Each form name starts with a company name and then has add’l descriptors (built into that name) for function and revision seperated by _ or -.

A simple example: CompanyA_Establishment_05-06.pdf

We might have 20 or more forms for CompanyA.

A search, by title, for “CompanyA” won’t find any of them! Same with “%CompanyA%” or “CompanyA%”.

I saw Michael’s recent comment to try searching by hand with:

“select * from Bug where Contains(sTitle, ’searchword’)”

With some experimenting, I’ve realized that only finds my search word in the title IFF it is surrounded by spaces.

As expected,
select * from Bug where Contains(sTitle, ‘%searchword%’)
doesn’t change things.

This works though:
select * from Bug where sTitle like ‘%searchword%’)
but is arguably a bit abusive on the db as we grow (right now we only have about 8k cases).

Is there any sort of way to improve the full text search? I’m game to edit code… Has anyone else been down this road of wanting to find letters IN a string?

I received a very quick response from an employee opining that this is really a short-coming of using MS SQL Server’s full text features. He sounded a bit (understandably) frustrated and closed with:

Using LIKE as you pointed out is not a solution as it will kill the db and performance on the machine. Am I just missing some secret syntax in FTS that would allow you to do what you want?

Well this inspired me a bit. I figured I’d do a bit of research — bust out the geek-fu as it were — and help these guys improve their product. All in my copious spare time (*cough*). Ok, well it did inspire me to check out SQL Server’s Books Online. I started by looking up the contains predicate. And lo and behold — there was the answer. In the “Word Prefix Match” section I see that we need to be adding an “*” to the end of our search clause. Sure, that looks painfully obvious I suppose… but if you’re used to working with SQL Server, you’re also used to using “%” as a wildcard! Tried it out quick and sure enough, we can search now.

I’m sure there’s a moral to this story. But “wait until everyone is about to mutiny” probably shouldn’t be it.  I guess I should’ve taken up the gauntlet much sooner!

2 Responses to “Learned a bit about SQL Server Full Text Search”

1. musgrave_wannabe on May 17th, 2006 10:12 am
   

   Could you POSSIBLY be a better techno-dork?

   It’s so bad, it’s like you went to high school in South Dakota, or something…

2. chris on May 17th, 2006 10:53 am
   

   Dang. I think I’ve been insulted! Maybe?

Leave a Reply