How to make a sql search query more powerful?
Solution 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.
Solution 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?
Solution 3
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%'
)
Solution 4
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.
Solution 5
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
Amr Elgarhy
Updated on May 30, 2020Comments
-
Amr Elgarhy almost 4 years
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.
-
Amr Elgarhy about 15 yearsyou write "soundex(name) = soundex('Spa')", in soundex(name) whats the name equal or should i wrote i like this?
-
Amr Elgarhy about 15 yearsfor the first script you wrote, it always give me this error:"Conversion failed when converting the varchar value 'L000' to data type int."
-
dkretz about 15 yearsOne 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 about 15 yearsA big problem is your "LIKE '%spa%'". Any "LIKE" key starting with a wildcard is an automatic table scan.
-
Booji Boy about 15 yearsI'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 about 15 yearsI 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.
-
dkretz about 15 yearsBut you fundamentally changed the question by removing the leading wildcard.
-
Booji Boy about 15 yearsPerhaps, 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.
-
dkretz about 15 yearsYou'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.
-
Booji Boy about 15 yearsok, 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.
-
RichieHH almost 7 yearsBit late but this is nonsense. If the need is to find something inside a field it's unavoidable