Prev: EXISTS Query vs Sub Query
Next: Capturing SQL Version
From: Doug on 16 Jul 2008 14:17
If someone put text in an image data type, is there a way to search that
column for specific data (full-text indexing would NOT be on the column)?

In particular, it appears the Content column of the tbl_Content table in
the TfsVersionControl database contains the source code for the Team
Foundation Server. I'm a bit surprised an image datatype is used in TFS
since MS recommends the new varbinary(max) under 2005.

Anyway.....I'd like to be able to look for specific text. Is there a way
using CAST,CONVERT, etc to get the data to the point where I can search for
text?

TIA,

Doug
From: Erland Sommarskog on 16 Jul 2008 18:56
Doug (Doug(a)discussions.microsoft.com) writes:
> In particular, it appears the Content column of the tbl_Content table in
> the TfsVersionControl database contains the source code for the Team
> Foundation Server. I'm a bit surprised an image datatype is used in TFS
> since MS recommends the new varbinary(max) under 2005.

That surprised me too.

I guess the story is that they were working on TFS while SQL 2005 was
in development, so may have started off with image, and then they never
came around to clean it up.

> Anyway.....I'd like to be able to look for specific text. Is there a
> way using CAST,CONVERT, etc to get the data to the point where I can
> search for text?

convert(nvarchar(MAX), convert(varbinary(MAX), Content))

I think this works.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 | 
Pages: 1
Prev: EXISTS Query vs Sub Query
Next: Capturing SQL Version