Geek asks and answers

How to determine the priority of choosing a LIKE request based on a string position in a field?

I am trying to query a table for a limited set of results to populate an autocomplete field in javascript. Therefore, I use the 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 this example, let's say I want to return two lines - and yes, for this example I created a country there;) I want to prioritize any instance where a partial line is matched at the beginning of the country, I started using the query:

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

This brought Cameroon and Canada back as expected. However, in cases where there are no two names in which the string is matched at the beginning of the word (for example, "de"), I want it to look elsewhere in the word. So I revised the request to become

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

"-" "", "ca", "Balanca" "Cameroon".

, : , , (, REGEXP?) , "" , , , (, ..).

+3
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
+7

More articles:


All Articles