Optimizing An Sql Search Using A Column For Keywords And A Variable As The Text To Be Searched

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

Solution 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@keywordsastable (keyword varchar(50))
INSERTINTO@keywordsVALUES ('quandary'),
       ('variable'),
       ('paragraph'),
       ('Narwhal')


DECLARE@inputasvarchar(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@keywordsSELECT kw.keyword
FROM 
(SELECT@input input) bigstring
INNERJOIN@keywords kw 
on bigstring.input like'%'+ kw.keyword +'%'



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

(4row(s) affected)

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

(3row(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

CREATETABLE table1 (keyword varchar(50))
INSERTINTO table1 
VALUES ('quandary'),
       ('variable'),
       ('paragraph'),
       ('Narwhal')

GO

Proc

CREATE  proc testKeyword
        @inputvarchar(1000),
        @debugvarchar(25) output

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

Test

DECLARE@debugvarchar(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',
           @debugoutSELECT@debug 

outputs 


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

(1row(s) affected)