RocketScientist's Miscellaneous Ramblings
posts - 76, comments - 354, trackbacks - 3

Substring Searching

I need a away to effectively search substrings.

The situation: I have a claim number field (consider it to be a random alphanumeric string). My user base was trained that since the claim number field could take wildcards, it's best to put them both before and after your search term, which effectively prevents any use of SQL Server indexes. The trainer needs to be introduced to the ballbat of doom. Now we can't fix the functionality to not allow the extra wildcards because that'd mean changing all of our trained user habits, and in addition our "project manager" (who is actually a client advocate, not a project manager) is simultaneously complaining about slow performance and exclaiming that the ability to do this type of search is absolutely critical.

Since the claim number is random alphanumerics, Microsoft Search Service interprets it all as noise, and won't index any of the values in the table. I've tried editing the noise word files, to no avail. All of the noise word files, for every language, on each server instance, and the ones in \windows\system and \program files\common files\data\microsoft search. While it will probably work in SQL Server 2005, it doesn't work in 2000.


Print | posted on Tuesday, October 04, 2005 1:32 PM |



# re: Substring Searching

I'm assuming you mean LIKE '%xxx%' queries. If thats the case:

1) It is not true that queries like this can't take advantage of an index. They can but they will do an index scan, not an index seek. An index scan is more expensive than an index seek but its still a lot better than a table scan

2) If LIKE '%SearchClaimNumber' is what you are doing, then you can create a computed column with formula "REVERSE(ClaimNumber)" then put an index on this. You can then rewrite your query as ReverseOfClaimNumber LIKE REVERSE(SearchClaimNumber) + '%'

10/5/2005 12:17 AM | Rommel Abesames

# re: Substring Searching

Thoughts...sure supply us with some examples...

What about FULL-TEXT indexing?

10/5/2005 8:34 AM | Brett
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET