Как расставить приоритеты при выборе запроса LIKE на основе позиции строки в поле?

Question

Я пытаюсь запросить таблицу для ограниченного набора результатов, чтобы заполнить поле автозаполнения в JavaScript. Поэтому я использую оператор LIKE с введенной частичной строкой.

Если у меня есть, например, таблица, такая как:

tblPlaces
id     country
1      Balanca
2      Cameroon
3      Canada
4      Cape Verde
5      Denmark

Для примера, скажем, я хочу вернуть две строки - и да, для этого примера я создал страну там;) Я хочу расставить приоритеты для любого экземпляра, в котором частичная строка совпадает в начале страны. Запрос, который я начал использовать, поэтому:

SELECT id, country FROM tblPlaces WHERE country LIKE 'ca%' LIMIT 2

Это вернуло "Камерун" и "Канаду", как и ожидалось. Однако в тех случаях, когда нет двух имен, в которых строка совпадает в начале слова (например, "de"), я хочу, чтобы она выглядела в другом месте слова. Поэтому я пересмотрел запрос, чтобы стать

SELECT id, country FROM tblPlaces WHERE country LIKE '%ca%' LIMIT 2

Затем это вернуло "Кабо-Верде" и "Данию", но при этом прервал мой первоначальный поиск "ca", который теперь возвращает "Balanca" и "Cameroon".

Итак, мой вопрос заключается в том, как это сделать, используя один запрос, который будет определять приоритет совпадения в начале слова (возможно, мне нужно использовать REGEXP?). Я также предполагаю, что, если столбец "страна" проиндексирован, эти совпадения будут по крайней мере возвращены с последующим алфавитным приоритетом (например, Камерун перед Канадой и т. д.).

Answer 1

Если вы хотите расставить приоритеты матчей, которые находятся именно в начале...

SELECT id, country
FROM tblPlaces
WHERE country LIKE '%ca%'
ORDER BY CASE WHEN country LIKE 'ca%' THEN 0 ELSE 1 END, country
LIMIT 2

РЕДАКТИРОВАТЬ

Более общий и, возможно, более быстрый (при условии, что "чем ближе к началу, тем лучше матч")...

SELECT id, country
FROM tblPlaces
WHERE country LIKE '%ca%'
ORDER BY INSTR(country, 'ca'), country
LIMIT 2