Context Search

Hello,

I have a web application that I need to search based on what the user entered in the input box.
e.g when the user enters in the box something like "Brain Boom"

I need to search the column in the DB table where there is anything word like
Brain or has Boom or all the above. How will I accomplish this?

Thanks

In transact-sql the query would look something like this

select * from sometable where seachcolumn like '%Brain%' or searchcolumn like '%Boom%'

This will give you all colums in records from sometable that has Brain or Boom in column named searchcolumn.

|||Since these values are in one Text box, How would I know that there are two words in the text box? Do I have to always loop through the text box to check if it is a tab/comma delimited list?|||

Yes,

T-SQL is not able to determine that by itself. You need to construct a proper query for it and execute it.

I am not sure if full-text search capabilities would be an option in this case. Maybe some other more skilled SQL developer are able to give you more options.

|||

Two possible solutions that I would use.

1. Full Text Search. This sounds like a very good case for using it. It allows you to just say:

where CONTAINS ( columnName, 'Brain Boom')

It also gives you lots of other powerful features. I would almost certainly suggest this method based on what you have told us...

2. Check the techniques here: http://www.sommarskog.se/arrays-in-sql.html

then you can take the string 'Brain Boom' and put it in a table form like:

value
--
Brain
Boom

Then join to the table

select key, count(*)
from table
join <tableofvalues> as tbl
on table.columnName like '%' + tbl.value + '%'
group by key

Then you can see the rows that have the most matches.