如何根据字段中的字符串位置确定LIKE查询选择的优先级? [英] How to prioritize a LIKE query select based on string position in field?

问题描述

我正在尝试在表中查询有限的结果集，以便在javascript中填充自动填充字段.因此，我正在使用LIKE运算符并输入了部分字符串.

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

这按预期返回了喀麦隆"和加拿大".但是，在没有两个名称在单词开头匹配字符串(例如"de")的情况下，我希望它在单词的其他位置查找.所以我将查询修改为

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

然后返回佛得角"和丹麦"，但这样做使我对"ca"的原始搜索中断，该搜索现在返回"Balanca"和喀麦隆".

This then returned 'Cape Verde' and 'Denmark', but in doing so broke my original search for 'ca', which now returns 'Balanca' and 'Cameroon'.

所以，我的问题是，如何使用单个查询来解决这个问题，该查询将在单词开头优先匹配(也许我需要使用REGEXP?)我还假设如果国家"列为索引后，这些匹配项至少会以随后的字母优先级(例如，加拿大之前的喀麦隆等)返回.

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).

推荐答案

如果您要优先考虑刚开始的匹配项...

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

编辑

更通用，并且可能更快(假设更接近开始，更好地匹配")...

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

这篇关于如何根据字段中的字符串位置确定LIKE查询选择的优先级?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！