July 19, 2011 at 11:29 am
I am trying to find the charindex of a string in varbinary field, while using the FTS query.
I do not know if this works.
Any idea?
July 19, 2011 at 11:39 am
Do you mean something like this?
DECLARE
@VB VARBINARY(100) = CAST('This Is My String' AS VARBINARY(100)),
@Sub VARBINARY(100) = CAST('Is' AS VARBINARY(100));
SELECT CHARINDEX(@Sub, @VB);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 19, 2011 at 1:11 pm
GSquared provided the goods.
I am curious though, do you mind sharing the FTS piece of it? Is it still relevant for the overall solution?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 20, 2011 at 3:41 am
GSquared (7/19/2011)
Do you mean something like this?
DECLARE@VB VARBINARY(100) = CAST('This Is My String' AS VARBINARY(100)),
@Sub VARBINARY(100) = CAST('Is' AS VARBINARY(100));
SELECT CHARINDEX(@Sub, @VB);
Actually, no. I have documents saved as varbinary(max), and I am trying find the location of the first occurrence of the search criteria in the varbinary field.
July 20, 2011 at 8:58 am
Please post the query you're trying. Obfuscate names as needed.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 21, 2011 at 6:54 am
geo123abram (7/20/2011)
GSquared (7/19/2011)
Do you mean something like this?
DECLARE@VB VARBINARY(100) = CAST('This Is My String' AS VARBINARY(100)),
@Sub VARBINARY(100) = CAST('Is' AS VARBINARY(100));
SELECT CHARINDEX(@Sub, @VB);
Actually, no. I have documents saved as varbinary(max), and I am trying find the location of the first occurrence of the search criteria in the varbinary field.
That's what this does. Just use your table and column instead of the @VB variable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 21, 2011 at 10:19 am
SOrry, my mistake.....
And thanks a lot, it works....
Now, I am trying for a method to point to this location in the document, when I open it in the front end..... I guess that is outside the purview of this forum....
Thanks a lot for the support.....
PS: I don't know how to indicate that the query is resolved......
July 21, 2011 at 10:47 am
You're welcome.
No way to indicate "resolved", on purpose. What if someone comes by tomorrow with a better answer than mine? Gives them the chance to post it.
Keep in mind that binary data is, by nature, case-sensitive. So searching for a string inside a binary can be tricky.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 21, 2011 at 11:32 am
Thanks, and I get both your points.
I have one more related question.
Is it possible for me to search for occurrence of full stop (0x2E in varbinary) between two charindexes within a varbinary column?
and, if possible, how is the likely performance of this on a table with a large number (1000+) of document files in the varbinary column?
July 22, 2011 at 7:14 am
Yes. First you find where your first character is, then the one you want to terminate with, and use Substring to pull out the piece in between. Then use Charindex again to get the full-stop inside that substring.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply