multiplekey word search - SQL Server Forums

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)

multiplekey word search

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-11 : 18:08:10

hi

i have table with a coloumn name hotels .

i have an aspx page doing search on this coloumn thru a stored procedure using a like.
can you please tell me how to search the single column with multiple keywords using stored procedure.

for example

if enter taj residency calcutta

it should return all taj residencys in calcutta no matter how they may stored in the database table
ie if they were stored like this in table
taj residency calcutta or like this taj residency west bengal calcutta...... both shud be returned

please help.
i cannot implemet full text indexing because of catalog population problem.

thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-12 : 00:13:11

Tried with something like:

... where col like '%taj%residency%' ...

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-12 : 02:56:15

i want that to be implemented in stored procedure not just in the select statement

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-12 : 04:06:17

Is this your first stored procedure ever?

CREATE PROCEDURE dbo.uspMyFirstLittleSearchProcedure
(
	@Search VARCHAR(200)
)
AS

SET NOCOUNT ON

SELECT	*
FROM	MyTable
WHERE	MyColumn LIKE '%' + REPLACE(@Search, ' ', '%') + '%'

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-12 : 04:08:08

What exactly is "catalog population problem"?

E 12°55'05.25"
N 56°04'39.16"

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-12 : 10:40:30

you did not seem to get me.

i have already written that stored procedure.please read my question again .
no matter in which order i enter the key words if the table contains them it shud return them only if the records contain all the keywords.
something like google search .

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-12 : 13:35:17

1) Create a split function.
2) Insert every splitted word in a table variable
3) Join that table variable with the table holding the records you want to search.
4) Join on CHARINDEX or PATINDEX

Performance will be horrible but it works.

What exactly is "catalog population problem"?

E 12°55'05.25"
N 56°04'39.16"

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-12 : 20:22:16

hey peso can you provide with an example please .

the problem with catalog population is it is taking way too long to get populated. NOt finishing at all. even incremental population

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-12 : 20:44:45

How big is the table?

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-12 : 23:02:04

20 million records

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-13 : 00:26:37

It'll take a while for initial population.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-04-13 : 14:02:40

is there any another way ?

thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-13 : 14:11:30

This is another way where you will get strings where all the search words are contained regardless of order. But limitation is you can have only upto max of 4 search words:-

CREATE PROCEDURE dbo.uspMyFirstLittleSearchProcedure
(
	@Search VARCHAR(200)
)
AS

SET NOCOUNT ON

SELECT	*
FROM	MyTable
WHERE	
MyColumn LIKE '%' + PARSENAME(REPLACE(@Search,' ','.'),1) + '%'
AND MyColumn LIKE '%' + PARSENAME(REPLACE(@Search,' ','.'),2) + '%'
AND MyColumn LIKE '%' + PARSENAME(REPLACE(@Search,' ','.'),3) + '%'
AND MyColumn LIKE '%' + PARSENAME(REPLACE(@Search,' ','.'),4) + '%'
 GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 02:31:58

Yes. And what about "1515.b Baker street"?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources