0

I've been playing with full text search on SQL Server 2012. I must say my attempts to target special characters in search queries were futile - SS does not understand them. What's more, sometimes the presence of special characters breaks the query entirely and SS reports an error. I haven't been able to understand when and how and what characters are okay and when they break things. I haven't found any way to target special characters in search short of escaping/replacing them with some other characters. I've also stumbled onto this article http://searchsqlserver.techtarget.com/tip/Using-full-text-search-for-symbols-in-SQL-Server which implies some special characters work like they do in regular expressions. I haven't been able to confirm this hypothesis in practice - escaping as suggested in the article does not work and those characters do not function as regex commands either.

Can anybody point me to a concise [working] explanation of how to deal with FTS and special characters? I haven't found anything useful on the web. I also have read through questions on SO, they were equally unhelpful.

Improve this question
  • What "special characters" (language or very tricky symbols)? What UI/programming language and connection type you use to execute the query against the DB? – user23243 Jun 21 '13 at 7:39
  • Common chars like +#$./ etc. which make sense for programming terms, for instance. I'm typing a query directly in SSMS. – guest Jun 21 '13 at 8:02
  • 7
    I think you want to add some queries you've tried. Without them your question is somewhat cryptic. (You can edit your own posts anytime.) – dezso Jun 21 '13 at 8:24
  • 2
    I second @dezso's request. Please post and example of queries that you use and the result you expect and don't receive. – Marian Jun 21 '13 at 8:59
  • 1
    Please re-word your question in a way that it can be answered, including the details that other users have asked for. As it stands, it sounds like, "Hey, I'm having some issues with x, but that's all I'll tell you. Tell me how x is supposed to work." That is not a good fit for our site. – Aaron Bertrand Jun 21 '13 at 14:48
9

It is difficult to answer the question specifically without a good description of what you are trying to achieve and why, but it appears that Full-Text Search may not be a good fit for you based on your need to search for characters that are either reserved for a special purpose within the full-text engine, treated as delimiters by word-breakers, or present as a stopword in the default system stoplist.

The Full-Text Search feature is primarily aimed at performing linguistic searches on words and phrases using the rules of a particular language like English, German, or French, for example. Searching for programming keywords like $partition in T-SQL will probably not work as you would hope because $partition is not a word or phrase in any of the available 50 (spoken) languages. If there were a T-SQL language option, things would be different.

It might be possible use a custom dictionary of 'new words' to meet your requirements, but this is not something I have directly experimented with myself. I think that's about as helpful an answer as I can offer without knowing more about your needs.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.