SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 multiplekey word search
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akpaga
Constraint Violating Yak Guru

USA
322 Posts

Posted - 04/11/2008 :  18:08:10  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 04/12/2008 :  00:13:11  Show Profile  Reply with Quote
Tried with something like:

... where col like '%taj%residency%' ...
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
322 Posts

Posted - 04/12/2008 :  02:56:15  Show Profile  Reply with Quote
i want that to be implemented in stored procedure not just in the select statement
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/12/2008 :  04:06:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/12/2008 :  04:08:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What exactly is "catalog population problem"?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
322 Posts

Posted - 04/12/2008 :  10:40:30  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/12/2008 :  13:35:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
322 Posts

Posted - 04/12/2008 :  20:22:16  Show Profile  Reply with Quote
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
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 04/12/2008 :  20:44:45  Show Profile  Reply with Quote
How big is the table?

Edited by - rmiao on 04/12/2008 20:45:05
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
322 Posts

Posted - 04/12/2008 :  23:02:04  Show Profile  Reply with Quote
20 million records
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 04/13/2008 :  00:26:37  Show Profile  Reply with Quote
It'll take a while for initial population.
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
322 Posts

Posted - 04/13/2008 :  14:02:40  Show Profile  Reply with Quote
is there any another way ?

thanks in advance
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/13/2008 :  14:11:30  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/14/2008 :  02:31:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes. And what about "1515.b Baker street"?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000