June 11, 2009 at 7:38 am
Hi folks,
I have a table with a field called oudescription, my user needs to search the field by inputting one or multiple keywords, I am hoping user can have some flexibility to input their keyword(s), for example, using phrase, or separate keywords.
Currently I am using WHERE (([oudescription] like '%' + @kw + '%'), which apparently is not sufficient. First of all is to distinguish multiple keywords, and then determine if the inputted @kw is a phrase or not, and so on.
Can anyone give me better idea?
Thanks in advance.
June 11, 2009 at 9:10 am
Have you considered full text search?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2009 at 9:21 am
If you want word matches then full-text search is the way to go. If you are OK with partial word matches (e.g search for 'miss' - results include 'mission' & 'permission') then your existing method will work - you just need to build an SQL statement with all the terms.
oudescription like '%' + @key1 + '%'
OR oudescription like '%' + @key2 + '%'
Search phrases: generally you'd want them enclosed in quotes or something to identify that a match on the whole phrase is required.
But its worth looking at full-text search in BOL - Here's a quick example I ran up on a test DB I keep to hand
-- Enabe the full text feature
EXEC sp_fulltext_database 'enable';
-- create a catalog
CREATE FULLTEXT CATALOLG ft AS DEFAULT;
-- the table you are indexing must have a unique index
CREATE UNIQUE INDEX pk_Book ON dbo.Book(BookCode);
-- and create your fulltext index
CREATE FULLTEXT INDEX ON dbo.Book
( Title, SubTitle, Comment )
KEY INDEX pk_Book;
-- Now you can search on keywords
SELECT * FROM dbo.Book
WHERE CONTAINS (Title, ' key1 OR key2 OR key3 ');
-- Or on a phrase
SELECT * FROM dbo.Book
WHERE CONTAINS(Title, ' "exact phrase" ')
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply