Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.
If this question can be reworded to fit the rules in the help center, please edit the 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?
–
MarkJun 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.
–
guestJun 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.)
–
dezsoJun 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.
–
MarianJun 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

1 Answer
1

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.