使用关键字列和变量作为要搜索的文本来优化SQL搜索 [英] Optimizing an SQL search using a column for keywords and a variable as the text to be searched

查看:409
本文介绍了使用关键字列和变量作为要搜索的文本来优化SQL搜索的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

这是我的窘境。我有一个变量,其中包含一段文字,并且我有一列充满了关键字。我想搜索列中包含的每个关键字与包含在我的变量中的整个文本。



我知道如何做到这一点的唯一方法是一次选择一列中的每一行,然后使用带有各自通配符的LIKE运算符来查看是否在变量中的文本中的任何位置找到列中的关键字。我尝试这样做的每一种方式最终都会导致效率低下。



另一种方法是,如果我可以将像FREETEXT这样的运算符的顺序颠倒过来像FREETEXT(@input,关键字),这将是非常棒的。但我似乎无法找到一种简单而有效的方法。



关于如何尽可能有效地做到这一点的任何提示?我非常感谢你的帮助!

编辑:

这里是我的存储过程,供参考: / p>

  GO 
@input varchar(1000),
@debug varchar(25)输出

AS
BEGIN

SELECT TOP 1 @debug = kw.keyword
FROM(SELECT @input input)bigstring
INNER JOIN table1 kw
on bigstring.input LIKE'%'+ kw.keyword +'%'

END


FREETEXT 关键字),有两种方法。样本数据返回关键字的索引后的第一个选择(如果不想找到未找到的关键字,则将其过滤掉)。第二个检查是否存在

 将@keywords声明为table(keyword varchar(50))
INSERT INTO @关键字
VALUES('quandary'),
('variable'),
('paragraph'),
('Narwhal')


DECLARE @input as varchar(max)
SET @input ='我的窘境。我有一个包含一段文本的变量,并且我有一列充满关键字的列。我想根据包含在变量'


SELECT关键字,CHARINDEX(关键字,@input,0)$ b $中的全部文本来搜索列中包含的每个关键字b FROM @keywords

SELECT kw.keyword
FROM
(SELECT @input input)bigstring
INNER JOIN @keywords kw
on bigstring.input like '%'+ kw.keyword +'%'



(4行受影响)
关键字
------ ----------------- --------------------
quandary 10
variable 29
第54段
Narwhal 0

(4行受影响)

关键字
----------- -------------
窘境
变量
段落

(3行受影响)

如果有CROSS APPLY解决方案,我也不会感到惊讶



更新
仅取得第一个关键字作为out param





  CREATE TABLE table1(keyword varchar(50 ))
INSERT INTO table1
VALUES('quandary'),
('variable'),
('paragraph'),
('Narwhal')

GO

程序

  CREATE proc testKeyword 
@input varchar(1000),
@debug varchar(25)输出

AS
BEGIN

SELECT TOP 1 @debug = kw.keyword
FROM(SELECT @input input)bigstring
INNER JOIN table1 kw
on bigstring.input LIKE'%' + kw.keyword +'%'

END

测试

  DECLARE @debug varchar(25)
EXEC testKeyword'Heres my quandary。我有一个包含一段文本的变量,并且我有一列充满关键字的列。我想搜索列中包含的每个关键字与包含在我的变量中的全部文本',
@debug out

SELECT @debug

输出


-------------------------
窘境

(1行受影响)


Here's my quandary. I have a variable that contains a paragraph of text, and I have a column full of keywords. I want to search each of the keywords contained in the column against the entirety of the text contained within my variable.

The only way that I know how to do this is to select each row of the column one at a time, and then use the LIKE operator with wildcards on each side to see if the keyword from the column is found anywhere in the text within the variable. Every way that I try and do this ends up seeming GROSSLY inefficient.

Another way of looking at this is, if I could reverse the order of an operator like FREETEXT to do something like FREETEXT(@input, keywords), that would be awesome. But I just can't seem to figure out a way to do it simply and efficiently like that.

Any tips on how to do this as efficiently as possible? I am very grateful for the help!

Edit:

Here is my stored procedure, for reference:

GO
    @input varchar(1000),
    @debug varchar(25) output

AS
BEGIN

    SELECT TOP 1 @debug = kw.keyword
    FROM (SELECT @input input) bigstring
    INNER JOIN table1 kw 
    on bigstring.input LIKE '%' + kw.keyword + '%'

END

解决方案

Here are two ways depending on what you looking to do (assuming SQL 2005+ from the FREETEXT keyword). The first select after the sample data returns the index of the keyword (filter out zeros if you don't want keywords that aren't found). The second just checks for the existance

Declare @keywords  as table (keyword varchar(50))
INSERT INTO @keywords 
VALUES ('quandary'),
       ('variable'),
       ('paragraph'),
       ('Narwhal')


DECLARE @input as varchar(max)
SET @input = 'Heres my quandary. I have a variable that contains a paragraph of text, and I have a column full of keywords. I want to search each of the keywords contained in the column against the entirety of the text contained within my variable'


SELECT keyword, CHARINDEX(keyword, @input , 0)
FROM @keywords

SELECT kw.keyword
FROM 
(SELECT @input input) bigstring
INNER JOIN @keywords kw 
on bigstring.input like '%' + kw.keyword + '%'



(4 row(s) affected)
keyword                                            
----------------------- --------------------
quandary                10
variable                29
paragraph               54
Narwhal                 0

(4 row(s) affected)

keyword
-----------------------
quandary
variable
paragraph

(3 row(s) affected)

I wouldn't be surprised if there was a CROSS APPLY solution as well

Update Getting only the first keyword out as an out param

Data

CREATE TABLE table1 (keyword varchar(50))
INSERT INTO table1 
VALUES ('quandary'),
       ('variable'),
       ('paragraph'),
       ('Narwhal')

GO

Proc

CREATE  proc testKeyword
        @input varchar(1000),
        @debug varchar(25) output

    AS
    BEGIN

        SELECT TOP 1 @debug = kw.keyword
        FROM (SELECT @input input) bigstring
        INNER JOIN table1 kw 
        on bigstring.input LIKE '%' + kw.keyword + '%'

    END

Test

DECLARE @debug varchar(25)
EXEC testKeyword 'Heres my quandary. I have a variable that contains a paragraph of text, and I have a column full of keywords. I want to search each of the keywords contained in the column against the entirety of the text contained within my variable',
           @debug out 

SELECT @debug 

outputs 


-------------------------
quandary

(1 row(s) affected)

这篇关于使用关键字列和变量作为要搜索的文本来优化SQL搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆