modification of Robvolk's code - help!

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

Transact-SQL (2000)

modification of Robvolk's code - help!

Author

Topic

dr_seusse
Starting Member

22 Posts

Posted - 2002-07-24 : 16:06:15

Hello fellow programmers.

I am trying to modify a keyword search script that I developed from robvolk's posting:
http://www.sqlteam.com/item.asp?ItemID=2652

Below is the code that I'm trying to change. Currently, it accepts a space delimited inquiry and searches it through a single column. It returns the recordsets in order of ranking ('hits').

I am trying to modify this such that it ONLY returns the ~exact hits!

eg. the inquiry "doctor seusse sql forum" would return:
"sql forum doctor seusse says hi" &
"sql xforumx doctorxxx seusse says bye"
but not "xyz forun doctor seusse says hi"

thanks

doc
---
SELECT a, b, count(*) hits
FROM SEQUENCE
INNER JOIN vw1 ON vw1.inquiry like '%' +
Substring(' ' + @inquiry + ' ',seq,
CharIndex(' ' , ' ' + @inquiry + ' ' , seq) - seq)
+ '%'

WHERE
seq <= len(' ' + @inquiry + ' ') and
Substring(' ' + @inquiry + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @inquiry + ' ' , seq) - seq > 0
Group by a, b
ORDER BY Hits DESC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-24 : 16:20:48

Well, yeah, if you MODIFY my code at all of course it's not gonna work...

shit, many times it doesn't work even if you don't modify it...

Did you mean to say Merkin's article by any chance?

http://www.sqlteam.com/item.asp?ItemID=5857

dr_seusse
Starting Member

22 Posts

Posted - 2002-07-24 : 16:38:47

opps, you're right Rob. It was Merkin's code. my bad :)

I've read through so many of these codes (and all of the sub links too), they all seem so similiar, and yet so complex/confusing at the same time. I've managed to wrap my head around most of these codes, but this one in particular is hard to manipulate AND get right at the same time.

your assistant is much appreciated

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-07-25 : 10:31:42

If you want an exact match you don't need to split up the string at all. You can just do this :

Select *
From MyTable

Where SearchField like '%' + @Inquiry + '%'

Simple

Damian

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-07-25 : 10:34:39

Sorry, just realised what you meant....give me 10 mins

Damian

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-07-25 : 10:44:25

OK, basically, what you need to do is make sure the count of "hits" equals the number of distinct keywords you enter. So you put that condition in the having clause and use the same sequence technique to count the keywords.

Something like this

SELECT a, b, count(*) hits
FROM SEQUENCE
INNER JOIN vw1 ON vw1.inquiry like '%' +
Substring(' ' + @inquiry + ' ',seq,
CharIndex(' ' , ' ' + @inquiry + ' ' , seq) - seq)
+ '%'

WHERE
seq <= len(' ' + @inquiry + ' ') and
Substring(' ' + @inquiry + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @inquiry + ' ' , seq) - seq > 0

Group by a, b

Having count(*) = (

Select count(distinct
Substring(' ' + @inquiry + ' ', seq,
CharIndex(' ' , ' ' + @inquiry + ' ' , seq) - seq))
FROM SEQUENCE
WHERE
seq <= len(' ' + @inquiry + ' ') and
Substring(' ' + @inquiry + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @inquiry + ' ' , seq) - seq > 0

)

Does that make sense ?

Damian

dr_seusse
Starting Member

22 Posts

Posted - 2002-07-25 : 11:18:50

excellent, excellent, excellent!
thank you very much for the quick reply

It makes perfect sense! I was thinking of the exact same method/idea, but I guess the expert always gets it first :P

It works great!
please stay tuned, since I am still trying to implement further tweaking and may require your assistant again.

doc

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-07-25 : 19:15:18

quote:

may require your assistant again.

When you find her, tell her I need her as well!

Glad I could help

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-25 : 22:02:28

quote:
When you find her, tell her I need her as well!

WHAT???? I'm not your assistant anymore? YOU BASTARD!

dr_seusse
Starting Member

22 Posts

Posted - 2002-07-30 : 14:17:04

Hi Damian

I've just noticed something weird is happening with the code that i have. Can you give me some input. Thanks in advance

:

------
SELECT a, b, count(*) hits
FROM SEQUENCE
INNER JOIN vw1 ON vw1.inquiry like '%' +
Substring(' ' + @inquiry + ' ',seq,
CharIndex(' ' , ' ' + @inquiry + ' ' , seq) - seq)
+ '%'

WHERE
seq <= len(' ' + @inquiry + ' ') and
Substring(' ' + @inquiry + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @inquiry + ' ' , seq) - seq > 0

Group by a, b

Having count(*) = (

Select count(distinct
Substring(' ' + @inquiry + ' ', seq,
CharIndex(' ' , ' ' + @inquiry + ' ' , seq) - seq))
FROM SEQUENCE
WHERE
seq <= len(' ' + @inquiry + ' ') and
Substring(' ' + @inquiry + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @inquiry + ' ' , seq) - seq > 0

)

order by hits DESC
----

The records don't seem to be returning according to the number of "hits". In fact they are simply returned sorted with record id ascending from vw1. vw1 is simply "select * from x" - no where nor order by clause.

for example:
record 1: doctor seusse
record 2: doctor doctor seusse seusse

with inquiry "doctor seusse", the record 1 is returned before record 2. Under query analyzer, the "hits" show as 2 for both records.

furthermore, when I "select top 100 percent...order by name" in vw1, the records are returned sorted by name.

Have you encountered this problem, or did i just mess something up along the way? How do i fix this problem? Do i need to reposition the count(*) hits in the code? Is my view screwed up?

Please help.

Thanks again

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-07-30 : 19:04:41

Hi

That would be right because this is looking for distinct keywords. In this case "doctor doctor suesse suesse" is the same as "doctor suesse" as they have the same number of distinct keywords.

Take a look at the code, it is joining where it finds a word match, once it has found that word, the record is joined to.

If you wanted to get seriously into relevance weighting you might want to look into Full Text searches.

Hope that helps

Damian

Edited by - Merkin on 07/30/2002 19:05:40

dr_seusse
Starting Member

22 Posts

Posted - 2002-07-31 : 10:04:52

Thanks Damian

I'll have a look at your suggestion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources