Database Search

[Learner]

Hi all,

I am very new in programming so please apologized me for such question.

I have Product Table in SQL Server 2000 with the following structure:

Product_Id Int
ProductName Varchar(300)

Some of the product names are as follows

5-Htp
Coenzym Q-10
.
.
.
etc

I am writing search engine code for searching names in Product table. The issue I have is if user search 5htp or 5 htp no search result found as there are no product of such name.

Is there any way to write SQL query or some other way to find such type of words in table so either to write 5htp or 5 htp results should come?

Thanks a lot.

Asif

 

[BadgerByte]

Hi Asif,

There is no perfect way to do this, however you can increase the chances of them finding what they're after using the REPLACE statement. for example in your case it seems to be "-" and " " that stand between a match and none at all. You could try the following:

SELECT * FROM Product WHERE
ProductName LIKE '%user_query_here%'
OR
Replace(Replace(ProductName, '-',''),' ','') LIKE '%' + Replace(Replace('user_query_here', '-',''),' ','') + '%'

This way you'll be performing the normal like query followed by a new one which in the case of your examples above would be comparing "5Htp" to "5Htp"

Be warned though I'm no SQL expert and this is probably far from efficient! however if it works it should significantly improve your matches.

Hope this helps.

Regards,

Andy