Optimizing an SQL search using a column for keywords and a variable as the text to be searched

Question

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

Conrad Frix · Accepted Answer · 2011-11-10T21:25:36.023

1

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)

edited Nov 10 '11 at 21:25

answered Nov 10 '11 at 18:50

Conrad Frix

51,984
12
96
155

What's the type of the input variable and the keyword table? Also what does "doesn't work" mean e.g. No records, error? – Conrad Frix Nov 10 '11 at 20:38
The second query looks great, and when I execute it as you have written it, it works fine, but when I throw it into my stored procedure and pass the input variable in from an external source, it doesn't work. Do I need to adjust how the input variable is handled if it isn't hard coded directly above the select statement? Here's the code I dumped into the SP, trying to set the debug variable to the first found keyword: `SELECT TOP 1 @debug = kw.location FROM (SELECT @input input) bigstring INNER JOIN table1 kw on bigstring.input like '%' + kw.location + '%'` – Joshua Nov 10 '11 at 20:48
By doesn't work, I mean that when I paste the code into my stored procedure, using the exact same input and keywords, it never finds a match. I hope that makes sense? No errors though. – Joshua Nov 10 '11 at 20:53
Perhaps it would help if I pasted my SP... I'm adding that to my OP now. – Joshua Nov 10 '11 at 20:55
If I use `SELECT @debug = count(kw.keyword)`, that works as I expect it to... So maybe I've done something wrong with the TOP bit of it? But it works outside of the stored procedure, so I'm really lost. – Joshua Nov 10 '11 at 21:09
`SELECT TOP 1 @debug = kw.keyword` does put the first result in to @debug. You can do `SELECT @Debug` to see the value. What was the output you're looking for – Conrad Frix Nov 10 '11 at 21:20
I was trying to get the debug variable to send the keyword that matched the input out of my stored procedure and to another program. This WORKS when I execute the code as a stand alone, and it also works to use count instead of top, but for some reason, when I try and assign the debug variable to the top keyword within my stored procedure, that's when it never kicks anything out. – Joshua Nov 10 '11 at 21:22
Ok well it worked for me. I updated my answer to include the test – Conrad Frix Nov 10 '11 at 21:25
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/4893/discussion-between-joshua-and-conrad-frix) – Joshua Nov 10 '11 at 21:41

Optimizing an SQL search using a column for keywords and a variable as the text to be searched

1 Answers1

Linked