views:

1897

answers:

6

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.

A: 

This should work:

Select * from TableName where Name Like '%spa%'
ORDER BY Name
Nathan Koop
the problem is that he want to sort by the quality of the match, not so much the matching. Order by name won't do what he wants.
Booji Boy
+1  A: 

The following should do the necessary, but it's inefficient, doing two full table selects and it also relies on your exact match being delimited by spaces. I think FullText indexing would help, but that has overheads of its own.

select distinct * from
(
Select * from TableName 
   where CHARINDEX('spa ', Name) > 0
   or CHARINDEX(' spa', Name) > 0
Union
Select * from TableName 
  where Name Like '%spa%'
)
MrTelly
+5  A: 

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.

Booji Boy
you write "soundex(name) = soundex('Spa')", in soundex(name) whats the name equal or should i wrote i like this?
Amr ElGarhy
"Name" is the fieldname
Booji Boy
for the first script you wrote, it always give me this error:"Conversion failed when converting the varchar value 'L000' to data type int."
Amr ElGarhy
I fixed it. 1 -> '1'
Booji Boy
But you fundamentally changed the question by removing the leading wildcard.
le dorfier
Perhaps, I thought is was superfluous. amrelgarhy? I will see if that makes a diff when I get a chance. But still, your answer while perhaps not incorrect could have been phrased in a less condescending tone or even more helpful. I's just trying to defend the new guy.
Booji Boy
You're inferring something I didn't intend to imply. I was trying to give the benefit of the doubt rather than "it's obvious you don't even realize ..." Other responses seem to say that at least a few others read it that way. Although it's always a good reminder to try harder.
le dorfier
ok, my bad then. sorry. I thought the numbers were just a list, not parts of the data to better denote the change in order. All the drama seems a little silly now ;D. It is an interesting problem.
Booji Boy
+5  A: 

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?

le dorfier
what do you mean?
Amr ElGarhy
One field value for "1 Space Company"? Why aren't these two fields - "1" and "Space Company"? It's impossible to create an index on just the name part, and it's apparently really two different data elements, or you wouldn't be asking about sorting on a partial field.
le dorfier
A big problem is your "LIKE '%spa%'". Any "LIKE" key starting with a wildcard is an automatic table scan.
le dorfier
I'm not sure CHARINDEX('spa ', Name) > 0 would be better. Just because it's a table scan doens't mean it's wrong. I figured this was a search type query anyways and 'Spa' would really be a @FindMe parameter. How about trying to be helpful instead of smarmy?
Booji Boy
He is being helpful teaching that the technique used and the underlying design is bad. Basing your search on a query like this is like building your home foundation on Jello.
HLGEM
I disagree. A scan type query is ok sometimes as long it has a narrow focus and isn't the primary means of searching. Oh ya, plus - he's wrong.
Booji Boy
+1  A: 

Going off the top example, at least in MSSQL2005 changing the CLUSTERED to NONCLUSTERED will make it do a table scan. CLUSTERED gives you an index seek. Looks like it matches the conditions of the question.

CREATE TABLE tblTest(ID INT, colname VARCHAR(20) )
CREATE CLUSTERED INDEX tstidx1_tblTest ON tblTest(colname);
INSERT tblTest SELECT 1,'Space Company'
INSERT tblTest SELECT 2,'Spa Resort'
INSERT tblTest SELECT 3,'Spa Hotel'
INSERT tblTest SELECT 4,'Spare Parts'
INSERT tblTest SELECT 5,'WithoutTheKeyword'

SELECT * FROM tblTest WHERE colname LIKE 'Spa%'
ORDER BY DIFFERENCE(colname,'Spa') DESC;

DROP TABLE tblTest
SomeMiscGuy
+1  A: 

You basically need to define (precisely) what your ranking function really is. What if you have a row that is "The Spa." or "spa.com"? Once you have that defined, you need to put that logic into your ORDER BY clause. For example:

SELECT
    name
FROM
    Some_Table
WHERE
    name LIKE '%spa%'
ORDER BY
    CASE
     WHEN name LIKE '% ' + @search_word + ' %' THEN 1   -- Notice the spaces
     ELSE 2
    END,
    name

Alternatively, you could write a ranking function and use that:

SELECT
    name
FROM
    Some_Table
WHERE
    name LIKE '%' + @search_word + '%'
ORDER BY
    dbo.GetNameMatchRank(name, @search_word)

Performance on very large result sets may not be too great, so this approach depends on your expected search result sizes.

Tom H.