SQL Server全文搜索FREETEXTTABLE搜索多个列

时间:2022-10-25 09:29:10

I'm using the below query to return results from a table using Full-Text-Search. In SQL2000 it was only possible to search one or all columns in a table. Is it possible in SQL 2008?

我正在使用以下查询来使用全文搜索从表中返回结果。在SQL2000中,只能搜索表中的一个或所有列。在SQL 2008中有可能吗?

I would like to search two tables, Problem and Solution (Both indexed and in the same table):

我想搜索两个表,问题和解决方案(索引和在同一个表中):

DECLARE @topRank int set @topRank=(SELECT MAX(RANK) 
FROM FREETEXTTABLE([Support_Calls], Problem, 'test', 1)) 
SELECT [ID] AS [Call No],Company_Name, Problem, Solution, CONVERT(VARCHAR(20),CAST((CAST(ftt.RANK as DECIMAL)/@topRank * 100) AS DECIMAL(13,0))) + '%' as Match 
FROM [Support_Calls] INNER JOIN FREETEXTTABLE([Support_Calls], Problem, 'test') as ftt ON ftt.[KEY]=[ID] ORDER BY ftt.RANK DESC;

From what I can see the FREETEXTTABLE does not accept more than one column?

从我所看到的FREETEXTTABLE不接受多列?

3 个解决方案

#1


14  

You specify them in parentheses; FREETEXTTABLE(tablename, (col1,col2,col3), 'expr') or use an asterisk to seach all columns in the index.

你在括号中指定它们; FREETEXTTABLE(tablename,(col1,col2,col3),'expr')或使用星号来搜索索引中的所有列。

#2


1  

From MSDN,

Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can only be referenced in the FROM clause of a SELECT statement like a regular table name. Queries using FREETEXTTABLE specify freetext-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row.

返回包含基于字符的数据类型的列的零行,一行或多行的表,这些列的值与指定的freetext_string中的文本的含义相匹配,但不是确切的措辞。 FREETEXTTABLE只能在SELECT语句的FROM子句中引用,如常规表名。使用FREETEXTTABLE的查询指定*文本类型的全文查询,该查询返回每行的相关性排名值(RANK)和全文密钥(KEY)。

They give the following syntax:

它们提供以下语法:

FREETEXTTABLE (table , { column_name | (column_list) | * } 
          ,'freetext_string' 
     [ , LANGUAGE language_term ] 
     [ ,top_n_by_rank ] )

So yes, what Alex K. said as well.

是的,Alex K.也说过。

#3


0  

If you created a FULLTEXT INDEX on different columns, then you can simple use CONTAINS or FREETEXT to look on one of them, all of them, or some of them. Like this:

如果您在不同的列上创建了FULLTEXT INDEX,那么您可以简单地使用CONTAINS或FREETEXT来查看其中一个,所有这些或其中一些。喜欢这个:

SELECT *
FROM YourTable
WHERE CONTAINS(*, @SearchTerm);

If you want to look on all the columns that are included in the FULLTEXT INDEX. or:

SELECT *
FROM YourTable
WHERE CONTAINS((ProductName, ProductNumber, Color), @SearchTerm);

If you want to specify the columns that you want to search. If you need the results in one column, you are gonna have to do a UNION and do a search for every column you want to be searched.

如果要指定要搜索的列。如果您需要在一列中显示结果,则必须执行UNION并搜索要搜索的每个列。

SELECT *
FROM YourTable
WHERE CONTAINS(ProductName, @SearchTerm)
UNION
SELECT *
FROM YourTable
WHERE CONTAINS(ProductNumber, @SearchTerm)
UNION 
SELECT *
FROM YourTable
WHERE CONTAINS(Color, @SearchTerm)

#1


14  

You specify them in parentheses; FREETEXTTABLE(tablename, (col1,col2,col3), 'expr') or use an asterisk to seach all columns in the index.

你在括号中指定它们; FREETEXTTABLE(tablename,(col1,col2,col3),'expr')或使用星号来搜索索引中的所有列。

#2


1  

From MSDN,

Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can only be referenced in the FROM clause of a SELECT statement like a regular table name. Queries using FREETEXTTABLE specify freetext-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row.

返回包含基于字符的数据类型的列的零行,一行或多行的表,这些列的值与指定的freetext_string中的文本的含义相匹配,但不是确切的措辞。 FREETEXTTABLE只能在SELECT语句的FROM子句中引用,如常规表名。使用FREETEXTTABLE的查询指定*文本类型的全文查询,该查询返回每行的相关性排名值(RANK)和全文密钥(KEY)。

They give the following syntax:

它们提供以下语法:

FREETEXTTABLE (table , { column_name | (column_list) | * } 
          ,'freetext_string' 
     [ , LANGUAGE language_term ] 
     [ ,top_n_by_rank ] )

So yes, what Alex K. said as well.

是的,Alex K.也说过。

#3


0  

If you created a FULLTEXT INDEX on different columns, then you can simple use CONTAINS or FREETEXT to look on one of them, all of them, or some of them. Like this:

如果您在不同的列上创建了FULLTEXT INDEX,那么您可以简单地使用CONTAINS或FREETEXT来查看其中一个,所有这些或其中一些。喜欢这个:

SELECT *
FROM YourTable
WHERE CONTAINS(*, @SearchTerm);

If you want to look on all the columns that are included in the FULLTEXT INDEX. or:

SELECT *
FROM YourTable
WHERE CONTAINS((ProductName, ProductNumber, Color), @SearchTerm);

If you want to specify the columns that you want to search. If you need the results in one column, you are gonna have to do a UNION and do a search for every column you want to be searched.

如果要指定要搜索的列。如果您需要在一列中显示结果,则必须执行UNION并搜索要搜索的每个列。

SELECT *
FROM YourTable
WHERE CONTAINS(ProductName, @SearchTerm)
UNION
SELECT *
FROM YourTable
WHERE CONTAINS(ProductNumber, @SearchTerm)
UNION 
SELECT *
FROM YourTable
WHERE CONTAINS(Color, @SearchTerm)