c# - Sql Query for unknown number of keywords -

i want write sql query unknown number of keywords. keywords (tags) stored in table

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

user can enter number of keywords search against table. if and used strict search. if use or search items match 1 keyword. want query dynamically shape , use maximum keywords given in search if not of them. vehicle item , has keywords. car, vehicle, conveyance, cycle, bike, truck. want enter keywords bike cycle in textbox should form query search vehicle item.

you can search or operators or equivalent in (...) expression, group rows item column, , compare row counts. row highest count has highest number of keywords search list:

select top 1      column1, count(*) mytable column2 in ('tag1', 'tag3') group column1 order count(*) desc 

to deal lots of keywords without exposing code sql injection need either generate sql dynamically, or use table-valued parameters.

if take first approach, in expression becomes in (@tag0, @tag1, @tag2) number of tags in search string. create sql command, , add individual tags parameters. see this answer more details on dynamic query approach.

if list of tags grows significantly, alternative approach table-valued parameter improve performance of query. this answer explains how that.