CODEWITHSUNDEEP
sql-serverfull-text-search
Eric
Eric

Reputation: 2077

Ways to search for a single character followed by a hyphen followed by more text using sql server full text search

We have data that might look something like:

A-10001
A-10002
A-10003
B-10001
B-10002
B-10003

And where we'd like to find all of the matches containing "A-100". Using full text search, we are doing something like:

where contains(e.*, '"A-100*"')

However, because "-" is considered a word breaking boundary and because A is a noise word (since it's a single character), the query does not return any results. I am wondering if there is any (easy) way to return the expected results.

Note, I understand that in this simplified scenario I could use like instead of contains, something like:

where 
  (e.myColumn1 like '%A-100%' 
   or e.myColumn2 like '%A-100%' 
   or e.myColumn3 like '%A-100%', 
   etc)

But for my situation that is not going to work (mainly for performance reasons and because there are lots of columns that I would need to look at).

I also understand I could break the search string into two words, and do something like:

where 
  contains(e.*, '"100*"') 
  and (e.myColumn1 like '%A-100%' 
       or e.myColumn2 like '%A-100%' 
       or e.myColumn3 like '%A-100%', 
       etc)

But again, since there are lots of columns, this is not an ideal solution.

I am interested in a solution for sql server 2005 and later, but if there's a sql server 2008 specific solution, I'd be interested in that too.

Thanks for any help, Eric

Upvotes: 0

Views: 295

Answers (1)

brian
brian

Reputation: 3695

I don't think you're going to find a very clean solution for this. I may be oversimplifying the problem, but my advice would be a derived (computed) column of the first 2 characters. If your priority is read performance, put a nonclustered on the computed column and include the full column. This would enable a seek for 'A-' whereas like will always require a scan. Even if you have several columns to evaluate, this should still perform better than a like predicate for each column.

Upvotes: 2

Related Questions