SQL查询匹配关键字？ [英] SQL query to match keywords?

问题描述

我有一个表格，其中的列是 nvarchar（max），其中的文字是从文档中提取的。我怎样才能创建一个选择查询，我会传递另一个关键字列表作为参数，并返回按匹配数量排序的行？

也许这可能与全文搜索？

解决方案

是的，全文搜索可能，可能是最好的答案。对于直接的T-SQL解决方案，您可以使用拆分功能并加入，例如假设一个数字表称为dbo.Numbers（您可能需要决定不同的上限）：

  SET NOCOUNT ON; 
 DECLARE @UpperLimit INT; 
 SET @UpperLimit = 200000; 
 
 WITH n 
（
 SELECT 
 rn = ROW_NUMBER（）OVER 
（ORDER BY s1。[object_id]）
 FROM sys .objects AS s1 
 CROSS JOIN sys.objects AS s2 
 CROSS JOIN sys.objects AS s3 
 
 SELECT [Number] = rn  -  1 
 INTO dbo。数字
从n 
其中<= @UpperLimit + 1; 
 
 CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers（[Number]）; 
  

以及使用该数字表的分割函数：

  CREATE FUNCTION dbo.SplitStrings 
（
 @List NVARCHAR（MAX）
）
 RETURNS TABLE 
 AS 
 RETURN 
（
 SELECT DISTINCT 
 [Value] = LTRIM（RTRIM（
 SUBSTRING（@List，[Number]，
 CHARINDEX（N'， '，@List + N'，'，[Number]） -  [Number]）））
 FROM 
 dbo.Numbers 
 WHERE 
 Number <= LEN（@List ）
 AND SUBSTRING（N'，'+ @List，[Number]，1）= N'，'
）; 
 GO 
  

然后您可以简单地说：

  SELECT键，NvarcharColumn / *，其他列* / 
 FROM dbo.table AS outerT 
 WHERE EXISTS 
（
 SELECT 1 
 FROM dbo.table AS t 
 INNER JOIN dbo.SplitStrings（N'list，of，words'）AS 
 ON t.NvarcharColumn LIKE'％'+ s.Item + '％'
 WHERE t.key = outerT.key 
）; 
  

作为程序：

  CREATE PROCEDURE dbo.Search 
 @List NVARCHAR（MAX）
 AS 
 BEGIN 
 SET NOCOUNT ON; 
 
 SELECT键，NvarcharColumn / *，其他列* / 
 FROM dbo.table AS outerT 
 WHERE EXISTS 
（
 SELECT 1 
 FROM dbo.table AS t 
 INNER JOIN dbo.SplitStrings（@List）AS s 
 ON t.NvarcharColumn LIKE'％'+ s.Item +'％'
 WHERE t.key = outerT.key 
）; 
 END 
 GO 
  

然后您可以传入 C＃中的@List （例如 EXEC dbo.Search @List = N'foo，bar，splunge'）。
$ b

这不会超快，但我相信它会比将所有数据提取到C＃并且手动双嵌套循环更快。

I have a table with a column as nvarchar(max) with text extracted from word documents in it. How can I create a select query that I'll pass another a list of keywords as parameter and return the rows ordered by the number of matches?

Maybe it is possible with full text search?

解决方案

Yes, possible with full text search, and likely the best answer. For a straight T-SQL solution, you could use a split function and join, e.g. assuming a table of numbers called dbo.Numbers (you may need to decide on a different upper limit):

SET NOCOUNT ON;
DECLARE @UpperLimit INT;
SET @UpperLimit = 200000;

WITH n AS
(
    SELECT
        rn = ROW_NUMBER() OVER
        (ORDER BY s1.[object_id])
    FROM sys.objects AS s1
    CROSS JOIN sys.objects AS s2
    CROSS JOIN sys.objects AS s3
)
SELECT [Number] = rn - 1
INTO dbo.Numbers
FROM n
WHERE rn <= @UpperLimit + 1;

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers([Number]);

And a splitting function that uses that table of numbers:

CREATE FUNCTION dbo.SplitStrings
(
    @List NVARCHAR(MAX)
)
RETURNS TABLE
AS
    RETURN
    (
        SELECT DISTINCT
            [Value] = LTRIM(RTRIM(
                SUBSTRING(@List, [Number],
                CHARINDEX(N',', @List + N',', [Number]) - [Number])))
        FROM
            dbo.Numbers
        WHERE
            Number <= LEN(@List)
            AND SUBSTRING(N',' + @List, [Number], 1) = N','
    );
GO

Then you can simply say:

SELECT key, NvarcharColumn /*, other cols */
FROM dbo.table AS outerT
WHERE EXISTS
(
  SELECT 1 
    FROM dbo.table AS t 
    INNER JOIN dbo.SplitStrings(N'list,of,words') AS s
    ON t.NvarcharColumn LIKE '%' + s.Item + '%'
    WHERE t.key = outerT.key
);

As a procedure:

CREATE PROCEDURE dbo.Search
    @List NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT key, NvarcharColumn /*, other cols */
    FROM dbo.table AS outerT
    WHERE EXISTS
    (
      SELECT 1 
        FROM dbo.table AS t 
        INNER JOIN dbo.SplitStrings(@List) AS s
        ON t.NvarcharColumn LIKE '%' + s.Item + '%'
        WHERE t.key = outerT.key
    );
END
GO

Then you can just pass in @List (e.g. EXEC dbo.Search @List = N'foo,bar,splunge') from C#.

This won't be super fast, but I'm sure it will be quicker than pulling all the data out into C# and double-nested loop it manually.

这篇关于SQL查询匹配关键字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！