Still have problem with Search query

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)

Still have problem with Search query

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-11-21 : 08:02:12

I am still struggling with writing a search query.

Let me explain again

I have following data in Products table:


ProductID:       ProductName:      Description:
1                monitors          good
2                Sensros           excellent
3                LCD               excellent

Now If a user search for 'good monitors' I should get 1 result (productID=1)

But if user search for 'excellent monitors', I should get 0 result, because there was no product that had 'excellent' AND 'monitor'

Similarly, if a user searches for: 'excellent LCD sensor' , the result should be 0 (because I want to return a row only if all the words are found in that row).

Any idea how I can write the SQL query

Currently, I cannot use Full Text search facility, becasue my shared SQL Server company does not seem to be providing it ( or they want to charge more for this service)

Kind regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-21 : 08:05:25

Declare @searcString varchar(100)
Set @searcString ='excellent LCD sensor'
Select columns from yourTable where '%,'+@searcString+',%' like '%'+Description+'%'

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-21 : 08:11:39

you could also split your search keywords into a table variable and then join to that table.

Go with the flow & have fun! Else fight the flow

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-11-21 : 08:20:17

well, I did start using the split function as follows:

select distinct p.ProductID, p.ProductName, p.ShortDescription,

FROM Products P

LEFT OUTER JOIN O2_xREF o2 ON p.productid = o2.stk_ref

inner join (Select data from dbo.Split(@SearchText,'#')) B on p.productname like '%' + B.Data + '%' or p.productid like '%' + B.Data + '%'

OR p.ShortDescription LIKE '%'+B.data+'%'

-------- search in the O2 -----
OR o2.STK_REF LIKE B.data+ '%'
OR o2.SENSOR_ LIKE '%' +B.data+ '%'
.......

It worked fine. But then I was told that I should only return a product if ALL the search keywords are found for that product. But the above code will return a product even if one of the search keywords are found.

I have searched on Google, but cannot find a solution to this seemingly simple problem.

regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-22 : 00:35:20

Group by Columns having count(*)=3 -- the number of words

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources