SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 free text in a varbinary(max) column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Oliver wang
Yak Posting Veteran

China
50 Posts

Posted - 05/22/2012 :  05:27:46  Show Profile  Reply with Quote
Hi Everybody,

I have inserted a English paragraph in a varbinary(max) type column and it is now showing like "0x536569736D69632044617461......". I used the following scripts to get the items I need:

SELECT *
FROM records
WHERE CONTAINS(chart, N'limitation')
GO

"limitation" exits in the English paragraph, but I got nothing after executing the query. Does anybody know how to make it work? Thank you very much.

Regards,

Oliver

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 05/22/2012 :  11:55:12  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
can you please show us the schema of records?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 05/22/2012 :  12:35:16  Show Profile  Visit robvolk's Homepage  Reply with Quote
When storing it in binary columns you have to specify a filter for the FT indexing engine to identify the type of data. More information here:

http://goo.gl/Nlu4M

You'd have to indicate that this data is a .txt document type and rebuild your full-text index.
Go to Top of Page

Oliver wang
Yak Posting Veteran

China
50 Posts

Posted - 05/22/2012 :  23:49:41  Show Profile  Reply with Quote
Hi robvolk,

Thanks for your response, actually in the table I do have a column to store file type such as ".doc". Is it possible to do some changes directly on my scripts so it will be easy to understand?

Hi yosiasz,

the schema of the table is dbo. The table structure is as below:
CREATE TABLE [dbo].[Records](
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[SerialNumber] [int] NULL,
[Chart] [varbinary](max) FILESTREAM NULL,
[type_column] [varchar](20) NULL,
)

regards,

Oliver
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 05/23/2012 :  15:12:53  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
check this out wonder if it will help

http://arcanecode.com/2009/05/28/full-text-searching-a-filestream-varbinary-max-column/

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

Oliver wang
Yak Posting Veteran

China
50 Posts

Posted - 05/23/2012 :  23:26:22  Show Profile  Reply with Quote
Thank you very much, yosiasz. Your link is really useful~
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000