Sql Query for unknown number of keywords

Question

I want to write sql Query for unknown number of keywords. The keywords (tags) are stored in table like this

               column1    column2 
               item1      tag1
               item1      tag2
               item1      tag3
                 .         .
                 .         .
                 .         .

Now the user can enter any number of keywords to search against the table. if the and is used it will do strict search. if I use or it will search items that match only one keyword. I want query that dynamically shape itself and use maximum keywords given in the search if not all of them. Like a Vehicle is the item and It has the keywords. Car, Vehicle, conveyance, Cycle, Bike, truck. Now I want to enter the keywords Bike Cycle in the textbox so it should form the query to search the vehicle item.

How should I write sqlquery for unknow number of keywords to search in this case? — Spirals Whirls, May 21 '13 at 10:03
I think the best method would be to parse user string into a table and join to that. You've got two good options: drop it into the table programatically before running the query or (my prefered method) use SQL to parse the string out into a CTE and join that. — Stickman, May 21 '13 at 18:18

score 3 · Accepted Answer · edited May 23 '17 at 11:43

3

You can do a search with an OR operators or an equivalent IN (...) expression, group the rows by the item column, and compare row counts. The row with the highest count has the highest number of keywords from your search list:

SELECT TOP 1
     column1, COUNT(*)
FROM mytable
WHERE column2 IN ('tag1', 'tag3')
GROUP BY column1
ORDER BY COUNT(*) DESC

To deal with lots of keywords without exposing your code to SQL injection you need to either generate your SQL dynamically, or use table-valued parameters.

If you take the first approach, the IN expression becomes IN (@tag0, @tag1, @tag2) up to the number of tags in your search string. Create a SQL command, and add individual tags as parameters. See this answer for more details on the dynamic query approach.

If the list of tags grows significantly, an alternative approach with a table-valued parameter could improve performance of your query. This answer explains how to do that.

edited May 23 '17 at 11:43

Community

1
1

answered May 21 '13 at 10:07

Sergey Kalinichenko

714,442
84
1,110
1,523

1

And how should they call this from their C# application, *without* exposing the database to SQL Injection? – RBarryYoung May 21 '13 at 10:19
This solution is cool.. but the number of keywords is unknown.. So what about that? – Spirals Whirls May 21 '13 at 10:21
@dasblinkenlight Yes, please domonstrate that for this query. Otherwise your answer is either incomplete, dangerous or misleading. – RBarryYoung May 21 '13 at 10:21
1

@RBarryYoung I think those who work with sqlserver should know about the parameterized query and prevention of sqlInjections a little bit. – Spirals Whirls May 21 '13 at 10:29
@RBarryYoung Avoiding SQL injections in queries is the same for all queries in the world; the OP wants one query to achieve a specific result. Following best practices in calling SQL from C# is an orthogonal issue, polluting unrelated answers with endless recommendations to avoid SQL injection etc. is counterproductive, unless OP asks for it explicitly. – Sergey Kalinichenko May 21 '13 at 10:30
@dasblinkenlight First, giving correct ***secure*** answers is not something that avowed experts should only give when asked "explicitly", it is a full-time professional responsibility. Secondly, parametizing a *list* to a SQL Query without introducing SQL Injection exploits is very non-trivial. The mere fact that you are so ardently trying to squirm out of your answer with it here is proof that at best you don't want to expend the effort, and at worst that you do not actually know how to do it yourself. – RBarryYoung May 21 '13 at 10:52
@SpiralsWhirls See my answer above. Correctly and securely parametizing a *list* to a SQL Server query is non-trivial. The ratio correct to incorrect methods demonstrated on this site alone is at least 10 to 1. – RBarryYoung May 21 '13 at 10:54
Forgive me @RBarryYoung. I did not mean to critisize anything. Just the focus was to check the solution from someone elses perspective because I doubted my own solution. So thought get someone elses idea.. and the dasblinkenlight solution was much better than mine. I agree with you that there is alot more to a professional execution of the above query. – Spirals Whirls May 21 '13 at 11:32
1

@RBarryYoung As far as parameterizing SQL queries with `IN` go, this site provides a [spectacular answer](http://stackoverflow.com/a/337792/335858) to it (unfortunately, it's not the selected one, but it has nearly three times as many votes). – Sergey Kalinichenko May 21 '13 at 13:19
@dasblinkenlight You are correct, I had missed your earlier edit which does address my concerns, and I have changed my vote accordingly. As for the linked answer I encourage you to add it to your answer as well. Though I prefer the answer following your link (table-valued parameters) I like the indicated one (dynamic parmetization) as well because I know that client-side developers will find it attractive. – RBarryYoung May 21 '13 at 13:29
@RBarryYoung Adding links to the "`IN` list" answers is a great idea, thank you very much! – Sergey Kalinichenko May 21 '13 at 13:38
@dasblinkenlight As an aside, the accepted answer at that link, however, is incorrect, injectable and a great example of what programmers do if they are not explicitly told why that's wrong and how to do it right. And may be why I am over-sensitive to this issue. – RBarryYoung May 21 '13 at 13:49
1

If you want to pass a list of items to SQL Server, use a table-valued parameter from code. Then you can avoid the need for dynamic SQL, and so the risk of SQL injection http://msdn.microsoft.com/en-us/library/bb510489.aspx – Meff May 21 '13 at 14:22
@Meff Thanks for the comment! The last link in the answer points to an SO answer that explains how to do an `IN` list with a table-valued parameter. – Sergey Kalinichenko May 21 '13 at 14:32

score 0 · Answer 2 · answered May 21 '13 at 10:08

Well with linq that would be something like (assuming you have a model class called Products) and the user has send an array of keywords

IQueryable<Product> SearchProducts (params string[] keywords)
{
 IQueryable<Product> query = dataContext.Products;

 foreach (string keyword in keywords)
  {
   string temp = keyword;
   query = query.Where (p => p.Description.Contains (temp));
  }
  return query;
}

For more elaborate scenarios look at

http://www.albahari.com/nutshell/predicatebuilder.aspx

that's a shame cause it is very useful and it will save you a lot of time. If you are interested in raw sql I will update my answer. — idipous, May 21 '13 at 12:52

score 0 · Answer 3 · answered May 21 '13 at 10:15

0

Did you want this?

SELECT * FROM TABLE WHERE x.Keywords in (Select * FROM ListOfWantedKeywords) --The list of wanted keywords is your dynamic search.

answered May 21 '13 at 10:15

kenny chung

76
6

score 0 · Answer 4 · answered May 21 '13 at 10:16

Are you looking for this?

And version:

SELECT
    SRC.*
FROM SRC
WHERE NOT EXISTS (
    SELECT TOP(1)
        1
    FROM (
        VALUES
            ('xxx')
            , ('yyy')
    ) AS KEYWORDS(Word)
    WHERE SRC.col NOT LIKE '%' + Word + '%'
)

Or version:

SELECT
    SRC.*
FROM SRC
WHERE EXISTS (
    SELECT TOP(1)
        1
    FROM (
        VALUES
            ('xxx')
            , ('yyy')
    ) AS KEYWORDS(Word)
    WHERE SRC.col LIKE '%' + Word + '%'
)

Sql Query for unknown number of keywords

4 Answers4