CODEWITHSUNDEEP
sqlsql-serverfull-text-search
chillfire
chillfire

Reputation: 406

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

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

Upvotes: 0

Views: 312

Answers (1)

marc_s
marc_s

Reputation: 755531

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

Upvotes: 1

Related Questions