Reputation: 406
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
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