Reading BLOB data

[TonyKhela]

Hi,

I need to read blob varbinary(max) data using TSQL.

To illustrate this, you can create a table and insert a blob document (c:\King.doc) within it using the following code:

Create table document
(DocumentID int not null primary key identity(1,1),
DocName varchar(255),
[FileExtension] varchar(10),
Document varbinary(max)
)
GO
INSERT INTO [Document]
( [DocName]
,[FileExtension]
,[Document])
SELECT
'king.doc'
,'.doc'
,BulkColumn FROM OPENROWSET(Bulk 'C:\\king.doc', SINGLE_BLOB) AS BLOB;

If you then create another table called Resumes:

Create table Resumes
(CVID int not null primary key identity(1,1),
CVName varchar(255),
[FileExtension] varchar(10),
CVAbstract varchar(255),
CVDocument varbinary(max)
)

This has a similar structure to the document table but has an additional column that should contain the first 255 characters of the document.

Now I can do a INSERT SELECT, ie.

INSERT INTO [Resumes]
( [CVName]
,[FileExtension]
,[CVDocument]
, CVAbstract)
SELECT [DocName] ,[FileExtension] ,[Document], ??? FROM Document WHERE DocName LIKE ‘CV%’

But how do I extract the first 255 characters out of the document?

I think I would need an iFilter and use the file extension field to determine the filetype ie pdf or xls or as in this case doc, but I am not sure of the exact TSQL I would need to use.

Any help would be appreciated. Thanks.

 

[philhege]

One way (SQL2K5 ff) would be to create a CLR SP that accepts the document, creates an interface based on file extension, and extracts/returns the first x characters as output.

I don't know of a method to do this in native T-SQL.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.