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:

Space Company.
Spa resort.
Spa hotel.
Spare Parts.
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.

Booji Boy · Accepted Answer · 2009-03-25T12:53:50.450

10

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.

edited Mar 25 '09 at 12:53

answered Mar 25 '09 at 02:37

Booji Boy

4,522
4
40
45

you write "soundex(name) = soundex('Spa')", in soundex(name) whats the name equal or should i wrote i like this? – Amr Elgarhy Mar 25 '09 at 02:44
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 Mar 25 '09 at 03:04
But you fundamentally changed the question by removing the leading wildcard. – dkretz Mar 25 '09 at 17:44
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 Mar 25 '09 at 18:57
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. – dkretz Mar 25 '09 at 19:36
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 Mar 26 '09 at 01:25

dkretz · Answer 2 · 2009-03-25T19:54:16.553

5

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?

edited Mar 25 '09 at 19:54

answered Mar 25 '09 at 03:07

dkretz

37,399
13
80
138

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. – dkretz Mar 25 '09 at 03:42
A big problem is your "LIKE '%spa%'". Any "LIKE" key starting with a wildcard is an automatic table scan. – dkretz Mar 25 '09 at 03:44
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 Mar 25 '09 at 12:38
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 Mar 25 '09 at 13:08
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 Mar 25 '09 at 13:40
Bit late but this is nonsense. If the need is to find something inside a field it's unavoidable – RichieHH Jun 25 '17 at 08:00

score 1 · Answer 3 · answered Mar 25 '09 at 02:34

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%'
)

score 1 · Answer 4 · answered Mar 25 '09 at 13:22

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

score 1 · Answer 5 · answered Mar 25 '09 at 18:46

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.

Nathan Koop · Answer 6 · 2009-03-25T02:30:35.003

-1

This should work:

Select * from TableName where Name Like '%spa%'
ORDER BY Name

edited Mar 25 '09 at 02:30

answered Mar 25 '09 at 02:24

Nathan Koop

24,803
25
90
125

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 Mar 25 '09 at 02:31

How to make a sql search query more powerful?

6 Answers6

Linked