Query Actual Index Values from Full-Text Index

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Actual Index Values from Full-Text Index

Author

Topic

splosh
Starting Member

8 Posts

Posted - 2009-01-05 : 09:35:30

Hi,

Firstly, I'm new to SQL Team, so apologies if this is posted in the wrong place. Feel free to let me know if so.

Is anyone aware of a method I can use to query the actual values within a full-text index?

My reason why is as follows:

I have a relatively weighty chunk of free text data, on which I've built a full text index.

I also have a long (and changeable) list of terms that I need to find within my data. Rather than run a looping process using select...where...contains to check each search term individually, I'd like to run a set based operation.

I'm assuming that my index data (simplistically) looks something like this:

ClusteredIxKey WordFromData
1 Bob
1 Steve
1 Mike
2 Dave
2 Susan
2 Louise
etc...

I figured that if I could pull the actual index data into a table and then join this to my list of terms, this would do the trick.

Any advice or suggestions would be appreciated.

Regards, Iain

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 09:37:45

i think what you're after is CONTAINTABLE which gives you a table which you can join to

http://doc.ddart.net/mssql/sql70/ca-co_16.htm

splosh
Starting Member

8 Posts

Posted - 2009-01-05 : 09:53:57

Hi visakh16,

Unfortunately, CONTAINSTABLE does something rather different and still only accepts a single search term.

I'm looking for a way to find all of my search terms at once.

Thanks, Iain

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 09:56:32

quote:
Originally posted by splosh

Hi visakh16,

Unfortunately, CONTAINSTABLE does something rather different and still only accepts a single search term.

I'm looking for a way to find all of my search terms at once.

Thanks, Iain

so what will be your input? list of values to search for? and you want to return all data which has pattern similar to any of entered values?

splosh
Starting Member

8 Posts

Posted - 2009-01-05 : 10:01:12

That's right, I'll be looking for a list of search terms, which I'll hold in a table. This might be quite a long list.

Thankfully, I only need to return exact matches rather than pattern matches.

Thanks, Iain

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 10:04:43

quote:
Originally posted by splosh

That's right, I'll be looking for a list of search terms, which I'll hold in a table. This might be quite a long list.

Thankfully, I only need to return exact matches rather than pattern matches.

Thanks, Iain

then why do you need full text search options? wont a simple join provide you with result?

something like


SELECT m.*
FROM [Your Main Table] m
JOIN [Your Search List Table] s
ON s.searchterm=m.value

splosh
Starting Member

8 Posts

Posted - 2009-01-05 : 10:19:45

The data is not broken into terms. I have free text data held in a varchar(max) column, e.g.

My data is "The quick brown Fox jumped over the lazy dog".

I need to find "quick", "brown" and "jumped".

I can return these individually with 3 select...where...contains queries, but want to run a set based operation to find all of my terms at once.

Note that my list of terms may be very large, so neither do I want to use a loop to create a dynamic sql string of select...where...contains...or...or...n

Ta, Iain

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 10:23:56

quote:
Originally posted by splosh

The data is not broken into terms. I have free text data held in a varchar(max) column, e.g.

My data is "The quick brown Fox jumped over the lazy dog".

I need to find "quick", "brown" and "jumped".

I can return these individually with 3 select...where...contains queries, but want to run a set based operation to find all of my terms at once.

Note that my list of terms may be very large, so neither do I want to use a loop to create a dynamic sql string of select...where...contains...or...or...n

Ta, Iain

so you mean you want to return those data that contain all search terms ("quick", "brown" and "jumped".) or atleast any one of them ("quick" or "brown" or"jumped".)

splosh
Starting Member

8 Posts

Posted - 2009-01-05 : 10:39:41

All records that contain any term

Ta, Iain

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 10:43:42

[code]
SELECT m.*
FROM [Your Main Table] m
JOIN [Your Search List Table] s
ON m.value LIKE '%'+s.searchterm+'%'
[/code]

splosh
Starting Member

8 Posts

Posted - 2009-01-05 : 11:14:14

That's one way to do it - but it's horrendously inefficient RBAR. This will also generate duplicates for records hitting more than one term, requiring an additional distinct clause - increasing the overhead still further.

I have lots of data and may well need to run this process many times. Hence the full text index approach. I'd wager a loop on the full text index would be orders of magnitude faster.

Thanks anyway though, appreciate you trying to help.

Any other suggestions welcome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 11:40:20

what about this?


DECLARE @SearchList nvarchar(max)


SELECT @SearchList= '"' + REPLACE(LEFT(t.u,LEN(t.u)-1),',','" OR "') + '"'
FROM (SELECT searchterm+','
      FROM [Your Search List Table]
      FOR XML PATH(''))t(u)

SELECT *
FROM [Your Main Table]
WHERE CONTAINS(column,@SearchList)

splosh
Starting Member

8 Posts

Posted - 2009-01-05 : 12:07:00

Very interesting way of generating the list, will definitely use that at some point.

Unfortunately, it looks like the contains clause can only accept a string of maximum length 4000 chars. My sample at the moment generates a string of 305795 chars from a list of 3312 values.

Do you know how and where the index values are stored? If I could query this somehow that would be ideal.

Thanks, Iain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources