I am attempting to query a table for a limited resultset in order to populate an autocomplete field in javascript. I am, therefore, using a LIKE operator with the partial string entered.
If I have, for example, a table such as:
tblPlaces
id country
1 Balanca
2 Cameroon
3 Canada
4 Cape Verde
5 Denmark
For the sake of this example, let's say I want two rows returning - and yeah, for this example, I made up a country there ;) I want to prioritize any instance where a partial string is matched at the beginning of country. The query I began using, therefore is:
SELECT id, country FROM tblPlaces WHERE country LIKE 'ca%' LIMIT 2
This returned 'Cameroon' and 'Canada' as expected. However, in instances where there are no two names in which the string is matched at the beginning of a word (such as 'de'), I want it to look elsewhere in the word. So I revised the query to become
SELECT id, country FROM tblPlaces WHERE country LIKE '%ca%' LIMIT 2
This then returned 'Cape Verde' and 'Denmark', but in doing so broke my original search for 'ca', which now returns 'Balanca' and 'Cameroon'.
So, my question is, how to go about this using a single query that will prioritize a match at the start of a word (perhaps I need to use REGEXP?) I am assuming also that if the 'country' column is indexed, these matches will at least be returned with subsequent alphabetical priority (i.e. Cameroon before Canada etc).
The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns. By default ORDER BY sorts the data in ascending order. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.
SQL Server SUBSTRING() Function The SUBSTRING() function extracts some characters from a string.
If you mean to prioritize matches that are Exactly at the start...
SELECT id, country
FROM tblPlaces
WHERE country LIKE '%ca%'
ORDER BY CASE WHEN country LIKE 'ca%' THEN 0 ELSE 1 END, country
LIMIT 2
EDIT
More generic and possibly faster (Assuming "closer to the start the 'better' the match")...
SELECT id, country
FROM tblPlaces
WHERE country LIKE '%ca%'
ORDER BY INSTR(country, 'ca'), country
LIMIT 2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With