Android : Nearest match query from SQLite DB if input string value is bigger than any of the DB values?

I have DB entries: James Andy Bob David

For input string "Bobby" i want t开发者_如何学编程o return the 'Bob' row. OR for input string "Candy" i want to return 'Andy' row.

I am using Android cursors but can also run the raw query.

---

You might want to try something like this:

SELECT name
FROM names
WHERE name LIKE '%andy%'
OR 'andy' LIKE ('%' || name || '%')
ORDER BY abs(length('andy') - length(name)), name
LIMIT 1

It will select the shortest match containing the string "andy" or the longest match that is contained within "andy", using alphabetical order as a tiebreaker. To get all rows remove the LIMIT clause.