Sorting results on initial terms within a word first

Question

I have an autocomplete dropdown that is generated based on user's input.

Now I want to order the results in such a way that results that start with the term should come first, and then the rest in alphabetical order.

Right now they're just sorted alphabetically in the SQL query.

So I want Vanco... results to come before the rest.

As you can see, the query uses LIKE %term% wild card select at the moment.

Answer 1

Try something along these lines:

ORDER BY 
  CASE WHEN COL LIKE 'term%' THEN 1 ELSE 2 END ASC,
  COL ASC