Search code examples
t-sqlsearchphrase

Phrase Search in SQL Server 2008 (T-SQL)

I have a varchar 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.

Solution 
  •  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.