Search website - returning 'Kenya' when looking for 'Enya'

[Phydos]

I have an ASP - SQL query (for a music/media webpage) which searches for keyword, the problem the site has is that it returns words within words, for example; search for Enya, and it returns Kenya (and lots of them). The search uses "LIKE '%Enya%'".

Is there any way of specifying that % should not include other alphabetic characters, i.e. only include: spaces, end of field, or beginning of field, comma, and other non alphabetic characters such as hyphen?

Any suggestions will be very much appreciated.

I would like to try out the full-text facility, but the shared server we are hosting on will not allow this as we don’t have permissions.

Thank you,
Phydos

 

[SQLBill]

You could use:

LIKE '[-#$%^&*()]Enya[-#$%^&*()]'

I don't know what you would use for space, beginning/end of field.

You might try:

LIKE '[^A-Z]Enya[^A-Z]'

The ^ means NOT, but I don't know if it would work as shown above.

-SQLBill

 

[Phydos]

Thanks for the reply SQLBill.

It seems this would work okay (LIKE N'%[^A-Z]Enya[^A-Z]%'), only there is the problem of not being able to identify the beginning and end of field (to allow), as it is ignoring Enya if it is the only word in the field (i.e. nothing before or after) or if it is the first or last word in the field (e.g. "Enya’s Hits", or "Hits from Enya&quot.

Any ideas on how to solve this? Perhaps LIKE is the wrong way to go about it? I am sure will be something that affects many sites and database systems.

Phydos

 

[MeanGreen]

Microsoft does allow this sort of query within FULLTEXT using contains. However, it does not sound like you have FULLTEXT enabled. So I was playing with your request and came up with this query that I was testing in the Northwind DB. Can you test it with your table and see if it gets your desired results?:

declare @search varchar(25)
declare @qrystr varchar(500)
select @search = 'ale'
select @qrystr = 'select * from orders where shipname like '''+ '%[ ]' + @search + ' %''' +
' or shipname like '''+ @search + '[ ]%''' + ' or shipname like ''' + '%[ ]' + @search + ''''
select @qrystr
exec (@qrystr)

Hope this helps.

 

[karluk]

The simplest way to solve the issue of 'Enya' being at the beginning or end of the string is to concatenate a delimiter like a space or a dash to the beginning and end of the string you are searching. The query would be something like

select * from your table
where '-' + search_column + '-'
like '[-#$%^&*()]Enya[-#$%^&*()]'

 

[Dimandja]

I think MeanGreen comes closest to the solution. Instead of trying to solve this in a single expression, use multiple ORs. There is a single expression solution each for "should not include other alphabetic characters, i.e. only include: spaces, end of field, or beginning of field, comma, and other non alphabetic characters such as hyphen?".

Dimandja

 

[Phydos]

Thanks for all the advice, I’ve tried out a few techniques, and the one below seems to work quite well (although I may need to speed it up a little as there are close to a million records, and indexes wont work as we are just using keywords within a field).


SELECT *, Name AS Artist
FROM tblMusicCalalogue
WHERE ('-' + Name + '-' LIKE N'%[^A-Z]Enya[^A-Z]%') OR
('-' + Name + '-' LIKE N'%[^A-Z]Abba[^A-Z]%')

Returns such as:

ENYA
ENYA.=TRIBUTE=
ABBA
ABBA.=TRIBUTE=
ABBA-ESQUE
REAL ABBA GOLD
ABBA REVIVAL BAND

I’ll look into other ways of using multiple key words, and I’ll tie them together as in MeanGreen’s example.

Thanks,
Phydos