How do you search a database column that allows markup?

Question

The text could be stored in the database using either markdown, bbcode, html, etc. Should I remove any allowed tags from the search terms? Does your markup parser have any method to assist in that task?

I was going to use like '%searchword%' queries. Does full text search offer any advantages for such a simple text search?

Update: It seems there is a real plethora of ways to do this. I'll clarify my situation a little more. It's for a company's job posting website, about 5 or 7 varchar columns are open to the search (4 of which will allow markup), approximately 150 active job postings at any given time.

Answer 1

I'd use some external Full-Text search tool -- for example, Lucene.

Answer 2

I would store a striped version of that field in separate field and use full-text index search. The stripped field would be updated to the latest version using triggers

Answer 3

If you have any type of markup (such as HTML) on your text, you may not get great linguistic accuracy during indexing and search. In that case, you have two choices—the preferred method is simply to store the text data in varbinary(max) column, and to indicate its document type so it may be filtered. If this is not an option, you may consider using the neutral wordbreaker and, if possible, adding markup data (such as 'br' in HTML) to your noise word lists.

See also:

• SQL Server 2005 Full Text Search on HTML documents

Alternatives:

• Lucene.Net