PatIndex on varbinary Column

Question

PatIndex on varbinary Column

umaramiya-724626

Old Hand

Points: 319
More actions
June 17, 2008 at 1:13 am

#188013

Hi,
I have a Table which holds varbinary column(word document). I need to search few keywords in this column.
How to use PatIndex on varbinary column?
Thanks,
Uma Ramiya

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Open a Word document in a text editor like Notepad and let me know if you think a search for any word would actually work...;)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

umaramiya-724626 Old Hand Points: 319 More actions · Answer 2

I found a solution through another form member. for those who have the same issue please go thru the following link

http://msdn.microsoft.com/en-us/library/ms187787.aspx

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Thanks for sharing...

But that's still going to have the same problem as PatIndex... For example... if you're looking for the word "GUID" or the word "DEFAULT" in a word document, you'll find that ALL word documents contain those words.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 4

Try something like this (adapted to your environment):

Declare @s-2 varchar(256),@v varbinary(256)

Select

@s-2='String with wierdness'+Char(127)+' in it...',

@v-2=cast(@s as varbinary)

Print @s-2

Print @v-2

Print patindex('%'+Char(127)+'%',cast(@v as varchar))

I've run into cases where the above PATINDEX may need to be augmented with a COLLATE predicate; I usually use collation "Latin1_General_BIN" in that case.

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.