search HTML stored as binary image in SQl2000/2005 (without fulltext)

Question

I am building a simple search tool to search through 'n' articles of html content. I have tried the fulltext search option and all was well until we went live and I have had a load of trouble with the webhost getting stuff sorted properly. So I might have to move to a host that does not have SQL fulltext support.

All of the articles are stored in a SQL 'image' column, all I want to do is run a LIKE'%keyword%' search on this column, but have no idea how to do this or if it is even possible.

Can SQLserver decode the binary and do a search on the fly?

Or will I be better off just storing a text only version of the content in a second column?

I have looked at the Lucene.net project but am not sure if this will work on a shared hosting platform.

any help will much appreciated.

cheers. craig

Answer 1

It depends on your version of SQL server - in 2000, you're probably out of luck. "Image" really is just a binary blob - no string functions or anything will work on it.

In SQL Server 2005, you could possibly convert this (either in the database schema or on the fly, with a CAST) to VARCHAR(MAX) - a text type up to 2 GB, which can deal with the normal string functions, and can be searched using WHERE CAST(blob AS VARCHAR(MAX)) LIKE '.......'

It won't be exactly lightning swift - but it might work. I would prefer changing the datatype of that column to VARCHAR(Max), though - all just text, up to 2 GB supported - should be good enough for a few HTML documents.

Marc