SQLTeam.com | Weblogs | Forums

Filter rows and sort them as per relevance

sql2008

#1

I have 5 keywords. Now I have a table where in a string column I need to search for these keywords through LIKE operator. For example if keywords are
"Ajay', "anurag", "amit", "arnab", "ast" and one of the column string value is "My name is Ajay" then Keyword matched is "Ajay".
The problem is if a string match more than one keyword then it should come on top. For example if column string values are `"Where is Arnab"
"Where is Ajay, amit and Anurag"
"Where is Ajay, and Anurag"

Display Output in below order

"Where is Ajay, amit and Anurag"
"Where is Ajay, and Anurag"
"Where is Arnab"`


#2
DECLARE @vt_keywords AS TABLE
( keyword VARCHAR(50) PRIMARY KEY CLUSTERED);

INSERT INTO  @vt_keywords(keyword)
VALUES('Ajay'),('anurag'), ('amit'), ('arnab'), ('ast');


DECLARE @vt_Table AS TABLE
(idCol INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
 ,searchCol VARCHAR(500) UNIQUE );

 INSERT INTO @vt_Table(searchCol)
 VALUES('Where is Arnab')
        ,('Where is Ajay, amit and Anurag')
        ,('Where is Ajay, and Anurag');


SELECT
    T.idCol
    ,T.searchCol
    ,COUNT(OA.no_rows) AS No_matching_rows

FROM
    @vt_Table AS T
    OUTER APPLY
    (SELECT
        1
     FROM
        @vt_keywords AS K
     WHERE
        --LEN(REPLACE(T.searchCol,K.keyword,'')) <> LEN(T.searchCol)
        T.searchCol lIke '%'+k.keyword+'%'
    ) OA(no_rows)
GROUP BY
    T.idCol
    ,T.searchCol
ORDER BY
    No_matching_rows DESC

The output for this:

idCol       searchCol                      No_matching_rows
2           Where is Ajay, amit and Anurag 3
3           Where is Ajay, and Anurag      2
1           Where is Arnab                 1