help on Query for search engin

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)

help on Query for search engin

Author

Topic

ssr972
Starting Member

2 Posts

Posted - 2004-12-07 : 03:33:30

Hi,

I need some help on a Query.
I need to make a kind of search engine.
Right now, I use a query that works for some searches.

Here's the contains of one of my tables.
IDK Actors
1 Brad Pitt
2 Kevin Costner
3 Robert de Niro
4 Tom Hanks
5 Al Pacino

If the user writes :
- Brad the seach returns Brad Pitt
- Bra the search returns Brad Pitt
- no the search returns Robert de Niro, Al Pacino
- Brad no the search returns Bradd Pitt, Robert de Niro, Al Pacino

So I would like to rewrite the query to perform this :
If the user writes
- no I don't want to retreive Robert de Niro neither Al Pacino
- Pacino I want to retreive Al Pacino
- Bra Pacino, I want to retreive Al Pacino
- Brad Pacino, I want to retreive Brad Pitt, Al Pacino

and if it's possible :
If the user write Pacinos, I would like to retrieve Al Pacino

If someone could help me on this topic.

Regards

Stan

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-07 : 03:46:32

try patindex in your where clause or use full-text

--------------------
keeping it simple...

ssr972
Starting Member

2 Posts

Posted - 2004-12-07 : 05:49:11

Thanks Jen..
Just a question... I ve tried to setup the full text and it seems to work fine.
So, my question is.. How to automatically update the catalogue, when I update the tables ?
I ve seen there's a scheduler but does it mean, when I add some records into the tables, the catalogue is not updated until the procedure starts ?

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-12-07 : 10:31:48

quote:
Originally posted by ssr972

Thanks Jen..
Just a question... I ve tried to setup the full text and it seems to work fine.
So, my question is.. How to automatically update the catalogue, when I update the tables ?
I ve seen there's a scheduler but does it mean, when I add some records into the tables, the catalogue is not updated until the procedure starts ?

Use a trigger?

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-07 : 22:02:45

quote:
Originally posted by ssr972

Thanks Jen..
Just a question... I ve tried to setup the full text and it seems to work fine.
So, my question is.. How to automatically update the catalogue, when I update the tables ?
I ve seen there's a scheduler but does it mean, when I add some records into the tables, the catalogue is not updated until the procedure starts ?

if you already have it scheduled and with a job, you can start the job when changes are made via trigger, sp_start_job.

--------------------
keeping it simple...

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-07 : 22:38:10

Here is an SQL Technique based on the following article:

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

create table t (IDK int, Actors varchar(20))
insert into t
select 1,'Brad Pitt' union all
select 2, 'Kevin Costner' union all
select 3, 'Robert de Niro' union all
select 4, 'Tom Hanks' union all
select 5, 'Al Pacino'

declare @searchstring varchar(20)
set @searchstring = 'Brad Pacino'

select t.*
from
(
	SELECT NullIf(SubString(' ' + @searchstring + ' ' , n , CharIndex(' ' , ' ' + @searchstring + ' ' , n) - n) , ' ') AS Word 
	FROM numbers
	WHERE n <= Len(' ' + @searchstring + ' ') AND SubString(' ' + @searchstring + ' ' , n - 1, 1) = ' ' 
	AND CharIndex(' ' , ' ' + @searchstring + ' ' , n) - n > 0
) d1
join
(
	SELECT IDK, 
	NullIf(SubString(' ' + Actors + ' ' , n , CharIndex(' ' , ' ' + Actors + ' ' , n) - n) , ' ') AS Word 
	FROM numbers, t
	WHERE n <= Len(' ' + Actors + ' ') AND SubString(' ' + Actors + ' ' , n - 1, 1) = ' ' 
	AND CharIndex(' ' , ' ' + Actors + ' ' , n) - n > 0

) d2 on d2.word = d1.word
join t on t.IDK = d2.IDK

drop table t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources