Search Query: Look for two words, relevancy

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 2000 Forums

SQL Server Development (2000)

Search Query: Look for two words, relevancy

Author

Topic

jmcbride
Starting Member

24 Posts

Posted - 2003-07-24 : 15:13:51

Hi, trying to setup a query that can take more that one word and search for them independantly. Then return the results ranked according to the relevancy of the words.

Example:
If I search for three words: Joe College Smith
My results could be:
smith Joe College
Joe College
Joe Smith
Smith
College
Joe

Do you see how they would be ranked by relevancy?

Thanks!

--------------------
http://www.utsa.edu/
(Joe) Joseph McBride

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-24 : 15:26:56

I'm assuming you mean to search 1 column in a table, yes?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-24 : 15:47:48

I don't know if this helps..but it finds the number of words in a string..

maybe you can modify it..but I think you're going to have to get real creative..


CREATE FUNCTION udf_WORDS
	(@str varchar(8000))
RETURNS int
AS
BEGIN
	DECLARE @Words INT, @Pos INT, @x Int
	SELECT @Words = 0, @Pos = 1, @x = -1

	WHILE (@x <> 0)
		BEGIN
			SET @x = CHARINDEX(' ', @str, @Pos)
			SET @Pos = @x + 1
			SET @Words = @Words + 1
		END

	RETURN @Words
END

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-24 : 15:54:01

Well

I have ZERO experience with this...

But look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_fulltextsearch.asp

Brett

8-)

jmcbride
Starting Member

24 Posts

Posted - 2003-07-24 : 16:16:30

quote:

I'm assuming you mean to search 1 column in a table, yes?

Brett

8-)

There are actually two columns in a table (first and last names).

--------------------
http://www.mejoe.com/
(Joe) Joseph McBride

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-24 : 17:12:30

Three words against two fields, dbo.table1.fistname and dbo.table1.lastname

Am I missing something???

Full text index = Slow D.B.

shazbat!

Jim
Users <> Logic

jmcbride
Starting Member

24 Posts

Posted - 2003-07-26 : 16:53:19

quote:

Three words against two fields, dbo.table1.fistname and dbo.table1.lastname

Am I missing something???

Full text index = Slow D.B.

shazbat!

Jim
Users <> Logic

Thats right. Any ideas?

--------------------
http://www.mejoe.com/
(Joe) Joseph McBride

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-26 : 17:40:00

OK, here we go:

First, the search terms must be put into a temp table, a regular table, or a table variable of some sort. there are articles and posts on how to do this from a comma-seperated string (CSV) -- do some searching.

Let's assume you have gotten your search terms into a table variable @Words, with a column called "Word".

Next, the table you are searching -- let's call it SearchTable, and you are looking in COlumn1 and/or Column2 for matches. I assume each row in SearchTable has a unique keyfield, let's call it ID.

So, take a look:

select ID, 1.0 * count(*) / (Select COUNT(*) FROM @Words) as MatchPct
from
SearchTable a
inner join
@Words b
on
a.Column1 LIKE '%' + b.Word + '%' OR
b.Column2 LIKE '%' + b.Word + '%'
group by ID
order by count(*) DESC

that will return the matching ID's, with what the matching percent is .... this might not be very efficient (the LIKE joins with the %'s will require table scans) but it should work.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources