I have rtf text converted to blob stored in a SQL field, and I need to search the text content of it.

Question

I have loads of .rtf files, which are stored in a SQL blob, in a field/Column in my database. 

How can I search for text within this document without converting all docs back in to their respective .rtf format.

Answer 1

This should do the trick:


SELECT * FROM
( SELECT ID , [VERSION] , NAME , deleted,
ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY [VERSION] DESC ) AS rown , [DOCUMENT]
FROM dbo.SOURCEDOCUMENTS ) x WHERE rown = 1
AND convert(varchar(max),convert(varbinary(max),[DOCUMENT])) LIKE '%something%'