Using full-text search for symbols in SQL Server

Problem:

How can I work around this issue?

Explanation:

Expression	Syntax	Description
Any Character	.	Matches any single character except a line break.
Zero or more	*	Matches zero or more occurrences of the preceding expression, making all possible matches.
One or more	+	Matches at least one occurrence of the preceding expression.
Beginning of line	^	Anchors the match string to the beginning of a line
End of line	$	Anchors the match string to the end of a line.
Beginning of word	<	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 break	\n	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 ^.
Or	|	Matches either the expression before or the one after the OR symbol (|). Mostly used within a group. For example, (sponge|mud) bath matches "sponge bath" and "mud bath."
Escape	\	Matches 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 expression	{}	Matches text tagged with the enclosed expression.
C/C++ Identifier	:i	Matches the expression ([a-zA-Z_$][a-zA-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 ^, +, ! and @, and therefore these symbols are ignored when part of an 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.FTS_Table
INNER JOIN CONTAINSTABLE(dbo.FTS_Table, (title),
'"New World \+"', 10) AS KEY_TBL ON FTS_Table.ID = KEY_TBL.[KEY]

Returns:

Example #2: Non-wildcard symbols

The following query returns no results:

SELECT ID, Title
FROM dbo.FTS_Table
INNER JOIN CONTAINSTABLE(dbo.FTS_Table, (title),
'"@"', 10) AS KEY_TBL ON FTS_Table.ID = KEY_TBL.[KEY]

This is because FTS does not index the "@" character, while the following query:

select * from dbo.FTS_Table where Title like '%@%'

Returns:

That's 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.FTS_Table
INNER JOIN CONTAINSTABLE(dbo.FTS_Table, (title),
'"New World!"', 10) AS KEY_TBL ON FTS_Table.ID = KEY_TBL.[KEY]
WHERE title LIKE '%New World !%'

Results:

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:

(10 row(s) affected)
Table 'FTS_Table'. Scan count 0, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(0 row(s) affected)
Table 'FTS_Table'. Scan count 0, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

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.FTS_Table
INNER JOIN CONTAINSTABLE(dbo. FTS_Table, (title), '"New World !"', 10)
AS KEY_TBL ON FTS_Table.ID = KEY_TBL.[KEY]
ORDER BY 3

Results:

If the column "ord" should not be returned, a Common Table Expression (CTE) can be used:

WITH fts_example (id, title,ord) AS
(SELECT ID, Title, CASE WHEN title like '%New World !%' then 1
ELSE 2
END
FROM dbo. FTS_Table
INNER JOIN CONTAINSTABLE(dbo. FTS_Table, (title), '"New World!"', 10)
AS KEY_TBL ON FTS_Table.ID = KEY_TBL.[KEY])
select ID, Title from fts_example
order by ord

In comparing the last two queries, there's no difference in the execution plan,

Related SQL Server tips: How to use full-text search in SQL Server from A-Z Simplify queries with common table expressions (CTEs) How to use rank function in SQL Server 2005

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 it's often not decreased at all.

ABOUT THE AUTHOR:

Michelle Gutzait Michelle Gutzait works as a team member of the SQL Server Database Experts at www.pythian.com, a worldwide company providing Remote Administration services for databases, operating systems, SANs and networks. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include SQL infrastructure and database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS/SSIS packages, administrative and infrastructure tools development, reporting services and more. Copyright 2008 TechTarget

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. Submit a Tip Digg This!     StumbleUpon     Del.icio.us    RELATED CONTENT Microsoft SQL Server Avoid cursors in SQL Server with these methods to loop over records Implementing security audit in SQL Server 2008 What's new in SQL Server 2008 Reporting Services? SQL Server replication methods: Snapshot, merge or transactional New security features in SQL Server 2008 leave some work for you How to disable the shrink database task in SQL Server 2000 and 2005 New datetime data types in SQL Server 2008 offer flexibility SQL Server out of memory: Troubleshoot and avoid SQL memory problems SQL Server errors, failures and other problems fixed from the trenches SQL Server consolidation: Why it's an optimization technique SQL Server performance and tuning Top 10 SQL Server Tips of 2008 Tutorial: Performance tuning SQL Server via queries, indexes and more SQL Server Management Studio 2008: New features, part 1 Tutorial: SQL Server indexing tips to improve performance SQL Server virtualization pros and cons: Weigh the performance impact How to configure storage in SQL Server DB with more writes than reads Avoid cursors in SQL Server with these methods to loop over records Solve SQL Server errors and more from the DBA trenches -- part 2 Tutorial: Learn SQL Server basics from A-Z FAQ: Troubleshooting SQL Server Reporting Services SQL Server overview The sqlcmd utility in SQL Server Top 10 SQL Server Tips of 2008 Tutorial: Performance tuning SQL Server via queries, indexes and more Tutorial: SQL Server indexing tips to improve performance How to create a SQL inner join and outer join: Basics to get started SQL Server Service Broker Tutorial and Reference Guide How to configure storage in SQL Server DB with more writes than reads Avoid cursors in SQL Server with these methods to loop over records Solve SQL Server errors and more from the DBA trenches -- part 2 Tutorial: Learn SQL Server basics from A-Z SQL Server overview Research RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com) RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.