Phrase Search in SQL Server 2008 (T-SQL)

I have a v开发者_运维问答archar column with 3 rows:

i eat orange,
orange,
oranges are nice

I want SELECT query to return the result in this order:

orange, oranges are nice, i eat orange

i.e. those matches that start with the 'keyword'=orange should come before those that contain the keyword which again should come before those that ends with the keyword.

How can I do this using T-SQL? I tried using the LIKE keyword but no success so far.

---

 WHERE column LIKE '%' + keyword + '%'
 ORDER BY CASE WHEN column = keyword THEN 0
               WHEN column LIKE keyword + '%' THEN 1
               WHEN column LIKE '%' + keyword + '%' THEN 2 END

But really, for this kind of search you want to use a full-text index.

---

Try the following order by clause (assuming your WHERE clause returns only matches)

ORDER BY charIndex(keyword,col_name),length(col_name)

This will put the earliest occurrence of the keyword first.