Search code examples
sqlvb.netsearchsearch-enginetable-valued-parameters

Using table valued parameters with wildcards?

The scenario...

I'm developing a web site in ASP.net and Visual Basic that displays product listings (hereafter referred to as "Posts") generated by the users. In this site, I have a search box that allows the user to find his post more easily. The search box is designed to allow the user to input specific keywords which will hopefully provide a more customized search. For example:

Example search box

In the example above, the user has specified that he would like to search for books with an author matching the name "John Smith" and also with the tags "Crime" and "Suspense".

Steps:

  1. My SearchBoxManager class retrieves these key names (Author, Tags) and the key values (John Smith, Crime, Suspense).
  2. I run a SQL query using those parameters looking for exact matching.
  3. I extract each word from the key values so I can search them separately.
  4. This is where I am having issues. I run a SQL query using those parameters looking for non-exact matching (e.g., '%John%', '%Smith%'). This step is to create a system of results based on relevancy. This is my first relevancy algorithm so I may be going about it the wrong way.

The problem...

In order for the third step to work properly, I would like to place each set of separated words from the key values into a table-valued parameter, and then use that table-valued parameter in the SqlCommandText surrounded by the wildcard '%'.

In other words, because the number of words in each key value will probably change each time, I need to place them in a table-valued parameter of some kind. I also think I could string them together somehow and just use them directly in the query string, but I stopped mid-way because it was getting a little messy.

The question...

  • Can I use wildcards with table-valued parameters, and if so how?

Also, if any of you have developed a relevancy ranking algorithm for local search boxes or know of one, I would be ever-grateful if you could point me in the right direction.

Thank you in advance.

Solution

  • so some sample data 

    create table authors ( id int, name varchar(200) )
insert into authors values (1, 'John Smith')
insert into authors values (2, 'Jack Jones')
insert into authors values (3, 'Charles Johnston')

    if you want to work with a table var we can make one and populate with a couple of search words

    declare @t table(word varchar(10) )
insert into @t select 'smith' union select 'jones'

    now we can select using wildcards

    select authors.* from authors, @t words 
where authors.name like '%'+words.word+'%'