database search

[Guest]

I've a table filed holds keywords seperated by comma, and a web search form
where a user can type one of multiple keywords in the search text field
seperated by comma aswell. How do i create a sql statement to bring results
that matches one or more keywords in the database?

Many thanks in advance..

huzz

 

[Dave]

More of a database question than a .NET question. You may be able to find your answer by posting in a more appropritate group.

Assuming your using SQL Server, check out the IN keyword in T-SQL and the LIKE keyword.

IN lets you group fields together using commas.
LIKE lets you perform wildcard comparisons between strings.

Examples:

SELECT * FROM MyTable WHERE @Search IN (NameField, DescriptionField)
SELECT * FROM MyTable WHERE NameField LIKE @Search + '%'

If your going to search a comma-seperated list of values stored in a single field, you may have to use Dynamic-SQL. There are
examples of how to use this in the SQL Server docs and on the web.

Check out the stored procedure named, ""

 

[Cowboy \(Gregory A. Beamer\)]

If you are storing in a garbage dump (table with lots of text,
non-separated), your best option is using a full text index to index the
comma separated keywords. Personally, I would consider a less taxing
database structure, like one that enumerates the keywords, but that is just
my feeling on this type of data. It may not be feasible in your situation.

Regardless of your choice, the user's list of keywords presents you with
options:

1. Write the SQL in your app and submit to the database
2. Turn the keyword list into XML and use the XML capabilities of the
database (both Oracle and SQL Server can use this option)
3. Create a stored procedure that puts the user list in a temp table and
join that table with the tables being queried

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***********************************************
Think outside the box!
***********************************************

 

[Dave]

Sorry, I submitted the post prematurely

Check out the stored proc named, "sp_executesql". There is a performance cost when using this approach.

Also check out the FullText search capabilities for an alternative method of searching for keywords in Sql Server.

I recommend just bringing down the data to your code, and checking the keywords there, if possible.


Good Luck