Problem:
I have a table with a full-text-search index defined on it. I would like to search an exact SQL expression including symbols such as ! " @, but the 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.
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:
[TABLE]
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:
[TABLE]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:
[TABLE]That's because the LIKE clause searches the character in the string without using the FTS index.
Workaro
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
und
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:
[TABLE]Performance
When looking at the performance implications of the WHERE clause, it shows no difference between the two methods:
[IMAGE]
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:
[IMAGE]
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:
[TABLE]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, 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.
[TABLE]
