在stackoverflow看到如下问题:
Babyfood, plums, bananas and rice, strained
Bananas, dehydrated, or banana powder
Bananas, raw
Bread, banana, prepared from recipe, made with margarine
CAMPBELL Soup Company, V8 SPLASH Juice Drinks, Strawberry Banana
CAMPBELL Soup Company, V8 SPLASH Smoothies, Strawberry Banana
CAMPBELL Soup Company, V8 V. FUSION Juices, Strawberry Banana
需要根据 banana 在title中出现的顺序排序,如 Bananas,raw 排在CAMPBELL...banana 前面,因为前者 banana在第一位,而后者在最后一位,SQL语句:
SELECT title,LOCATE('banana',title)
FROM myTable
WHERE LOCATE('banana',title) > 0
ORDER BY LOCATE('banana',title)
也可以
SELECT title, INSTR(title, 'banana') as index
FROM table
WHERE title LIKE '%banana%'
ORDER BY index != 0, index