|
I've got a table of country names, and I want to be able to search for a country based on
only a partial name.
That is, if I type in "Aus", I expect to see both "Austria" and "Australia" in the search results.
The basic way to do this is to use wildcards:
SELECT * FROM Country
WHERE Name LIKE '%Aus%'
That query will return the details of every country whose name contains the letters "Aus".
Now suppose I do another search, this time for "Dominica". Plugging the new search string into
my query, I get both "Dominica", and "Domican Republic".
What if I want to make sure that my query puts "Dominica" at the top of the list, since that is an
exact match?
(I've written the following example in T-SQL)
DECLARE @Search varchar(30)
SELECT @Search='Dominican'
SELECT * FROM Country
WHERE Name like '%'+@Search+'%'
ORDER BY
CASE When Name=@Search Then 0 Else 1 END,
CASE When Name like @Search+'%' Then 0 Else 1 END
The first CASE ensures that exact matches are listed first. The second CASE ensures that countries which
start with my search string are listed before countries which contain my search string somewhere
in there middle.
If you signed up to this mailing list more than five weeks ago, then you'll notice that this is just
another variation on a previous tip titled "Fun With ORDER
BY". Stay tuned next week for something a
little different.
|