Full text searching using values from multiple tables

Dev Shed Forums Sponsor:

November 7th, 2004, 04:32 PM

infinity003

Contributing User

Join Date: Dec 2003

Posts: 100

Time spent in forums: 5 h 53 m 45 sec
Reputation Power: 5

Full text searching using values from multiple tables

Hi All-

I have a bit of an issue and hope that someone can shed some insight into the matter. Basically, I had created a very simple search application that searched a database using full text. What I did originally with mySQL was to take the fields from the tables with which I wanted to search (had criteria that I wanted to search upon) and then join those fields, creating a new table called search_table. This table had a full text index on it and made searching very fast and accurate. It would allow me to search for a string and spit back the records related to that string whose accuracy was calculated via the joined table's full text index.

Now, I need to search the same fields with a MS SQL database. Unfortunately, this time around I don't have the luxury of being able to create new table just for searching. From what I understand I can not create a full text index on a "view", but I was reading an article that indicated that it might be possible to create a full text index that encompasses different fields from different tables and calculates relevancy based upon that. Does anyone have any ideas as to how I might go about modifying this search to make it work? Thanks in advance.

infinity003

November 9th, 2004, 04:57 PM

fbraun

Contributing User

Join Date: Oct 2004

Posts: 303

Time spent in forums: 16 h 27 m 15 sec
Reputation Power: 0

Once you've pulled the recordset, concatenate the desired fields together into a single variable, then search the variable.

Code:


searchThis = RS("Field1") & RS("Field2') & RS("Field3")
if instr(searchThis, "search term") > 0 then
      ' display this record
end if

Something like that.

November 9th, 2004, 05:44 PM

infinity003

Contributing User

Join Date: Dec 2003

Posts: 100

Time spent in forums: 5 h 53 m 45 sec
Reputation Power: 5

Do this in the SQL engine? Any chance you could explain a bit more? Thanks a ton!

Viewing: Dev Shed Forums > Databases > MS SQL Development > Full text searching using values from multiple tables

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump