How to make a sql search query more powerful?

Question

I wrote this sql query to search in a table:

SELECT * FROM TableName WHERE Name LIKE '%spa%'

The table contain these row for example:

1. Space Company.
2. Spa resort.
3. Spa hotel.
4. Spare Parts.
5. WithoutTheKeyword.

I want to know how to edit this query so it return the results sorted like this:

2 Spa resort

3 Spa hotel

1 Space Company

4 Spare Parts

Means the items which contain the exact word first then the like ones.

Answer 1

something like

Select * from TableName where Name Like 'Spa%'
ORDER BY case when soundex(name) = soundex('Spa') then '1' else soundex(name) end

should work ok.

actually this will work better

Select * from TableName where Name Like 'Spa%'
ORDER BY DIFFERENCE(name, 'Spa') desc;

FWIW I did some quick tests and if 'Name' is in a NONCLUSTERED INDEX SQL will use the index and doesn't do a table scan. Also, LIKE seems to use less resources than charindex (which returns less desirable results). Tested on sql 2000.

Answer 2

You realize, I presume, that your schema just about eliminates any usefulness of indexes for these kinds of queries?

A big problem is your "LIKE '%spa%'". Any "LIKE" key starting with a wildcard is an automatic table scan.

---

EDIT: I read your question to say that there is a single field, Name, with field values something like "1 Space Company", "2 Spa resort", etc. with a number followed by words. And you needed the wild card in front of your search key to get past the number part. (This is to clarify my first comment.) Am I guessing correctly or not?