Using full text search for symbols in SQL Server. Problem By submitting your personal information, you agree that Tech. Target and its partners may contact you regarding relevant content, products and special offers. You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy. I have a table with a full text search index defined on it. Character Mapping Symbols Windows 7' title='Character Mapping Symbols Windows 7' />I would like to search an exact SQL expression including symbols such as FTS is ignoring these symbols by default. For example, if I search for the exact phrase SQL Server using CONTAINSTABLE, I get all expressions containing SQL Server only the exclamation point is ignored in the search. PDF files that contain the Visual Studio 2005 documentation. A character encoding tells the computer how to interpret raw zeroes and ones into real characters. There are many different types of character encodings floating. Character set vs. A character set is a onetoone mapping between a set of distinct integers and a set of written symbols. For example, define a new. Define character. English dictionary definition of character. The combination of mental. How can I work around this issue Explanation FTS differentiates between a symbol defined as a wildcard and the other symbols. Here are a few symbols used in FTS as wildcards Expression. Syntax. Description. Any Character. Matches any single character except a line break. Zero or moreMatches zero or more occurrences of the preceding expression, making all possible matches. Character Mapping Symbols Windows 7' title='Character Mapping Symbols Windows 7' />One or moreMatches at least one occurrence of the preceding expression. Beginning of lineAnchors the match string to the beginning of a line. End of lineAnchors the match string to the end of a line. Beginning of wordlt lt td Matches only when a word begins at this point in the text. End of word Matches only when a word ends at this point in the text. Line breakn. Matches a platform independent line break. In a Replace expression, inserts a line break. Any one character in the set Matches any one of the characters within the. To specify a range of characters, list the starting and ending character separated by a dash, as in a z. Any one character not in the set. Matches any character not in the set of characters following the. OrMatches either the expression before or the one after the OR symbol. ASCII s k i listen ASSkee,6 abbreviated from American Standard Code for Information Interchange, is a character encoding standard for electronic. A visual learning tool that inspires students to develop and organize their ideas. It supports visual thinking techniques, enabling students to easily create and. Mostly used within a group. For example, spongemud bath matches sponge bath and mud bath. EscapeMatches the character that follows the backslash as a literal. This allows you to find the characters used in regular expression notation, such as and. For example, Searches for the character. Tagged expressionMatches text tagged with the enclosed expression. CC Identifier i. Matches the expression a z. A Za z. A Z0 9. Quoted string q. Matches the expression. Space or Tab b. Matches either space or tab characters. Integer z. Matches the expression 0 9. The problem is that full text Search in SQL Server does not index symbols, such as, , FTS query. The wildcard symbols can be treated as regular symbols if the escape character appears before. Example 1 Wildcards. The following query uses the escape character to indicate that the following wildcard symbol should be treated as a simple character SELECT ID, Title. FROM dbo. FTSTable. INNER JOIN CONTAINSTABLEdbo. FTSTable, title,New World, 1. AS KEYTBL ON FTSTable. ID KEYTBL. KEYReturns IDTitle. Welcome to The Brave New World New World Modernisms New World Studies5. It is a New World 3. Conquest of the New World, 1. A Brave New World. Brave New World. 12. Brave New World Brave New World Revisited. Installing Lag Shield Anchor. The End of the New World. Example 2 Non wildcard symbols. The following query returns no results SELECT ID, Title. FROM dbo. FTSTable. INNER JOIN CONTAINSTABLEdbo. FTSTable, title, 1. AS KEYTBL ON FTSTable. ID KEYTBL. KEYThis is because FTS does not index the character, while the following query select from dbo. FTSTable where Title like Returns IDTitle. Face many questions Issues Work2. Book Coping with Technology Work. Meet my place. 60. One Night a Call Center. Special Fitness program www. Special. Fitness. Thats because the LIKE clause searches the character in the string without using the FTS index. Workaround. It is possible to add a WHERE clause with LIKE as follows SELECT ID, Title. FROM dbo. FTSTable. INNER JOIN CONTAINSTABLEdbo. FTSTable, title,New World, 1. AS KEYTBL ON FTSTable. ID KEYTBL. KEYWHERE title LIKE New World Results IDTitle. Welcome to The Brave New World Performance. When looking at the performance implications of the WHERE clause, it shows no difference between the two methods Figure 1 Query execution plan shows no performance difference using the WHERE clause in FTS. Statistics IO 1. Table FTSTable. Scan count 0, logical reads 3. Table FTSTable. Scan count 0, logical reads 3. SQL Profiler Figure 3 SQL Profiler shows no performance impact when using the WHERE clause in FTS. Click on image for enlarged view. Ranking symbols. There are cases when you must return all phrases containing the exact phrase no matter if there are symbols or not but you can rank the exact phrase containing the symbol higher. For that matter, a CASE can be added to the query and the results can be ordered by it SELECT ID, Title,CASE WHEN title like New World then 1. ELSE 2. END AS ord. FROM dbo. FTSTable. INNER JOIN CONTAINSTABLEdbo. FTSTable, title, New World, 1. AS KEYTBL ON FTSTable. ID KEYTBL. KEYORDER BY 3. Results IDTitleord. Welcome to The Brave New World New World Modernisms New World Studies2. It is a New World 2. Brave New World Brave New World Revisited. A Brave New World. Dell Inspiron 3521 Core I3 Wifi Drivers For Windows 7'>Dell Inspiron 3521 Core I3 Wifi Drivers For Windows 7. The End of the New World. Conquest of the New World, 1. Brave New World. 2If the column ord should not be returned, a Common Table Expression CTE can be used WITH ftsexample id, title,ord ASSELECT ID, Title, CASE WHEN title like New World then 1. ELSE 2. ENDFROM dbo. FTSTable. INNER JOIN CONTAINSTABLEdbo. FTSTable, title, New World, 1. AS KEYTBL ON FTSTable. ID KEYTBL. KEYselect ID, Title from ftsexampleorder by ord. In comparing the last two queries, theres no difference in the execution plan, statistics IO or SQL Profiler. If you have to query exact phrases including symbols using full text Search in SQL Server, you need to add your own logic, such as a WHERE clause with LIKE containing your symbol, a CASE in the SELECT list, etc. Performance is not dramatically decreased and its often not decreased at all. ABOUT THE AUTHORMichelle Gutzait works as a senior database consultant for. ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 2. For the last 1. 0 years, she has worked exclusively with SQL Server. Her skills include SQL Server infrastructure design, database design, performance tuning, security, high availability, VLDBs, replication, T SQLpackages coding, and more.