SQL Server query order by column containing string

Question

I'm filling my ASP.NET dropdownlist from a column (nvarchar) in my SQL Server database.

Is there any way that I can bring rows containing a keyword to the beginnning of my returned result set?

For instance I have these records in my table:

abc
abc1
abcd2
abcXYZ3
adfdf3XYZ
abcd5

I want to have rows containg XYZ at the top of my dropdownlist, i.e:

abcXYZ3
adfdf3XYZ
abc
abc1
abcd2
abcd5

Is it possible to create a SQL query for this purpose, for instance something like this:

select * 
from myTable 
order by (mycolumn LIKE '%XYZ%')

How can I get my desired result? If it is not possible in SQL Server, how can I do it in my C# code?

Answer 1

To do it in SQL you could use

SELECT *
FROM   myTable
ORDER  BY CASE
            WHEN mycolumn LIKE '%XYZ%' THEN 0
            ELSE 1
          END,
          mycolumn 

Answer 2

You could order by the number of occurences:

SELECT column,
       len(column) - len(replace(column,@text,'')) AS MatchNumber

FROM   table

ORDER BY len(column) - len(replace(column,@text,''))

This has the advantage of ordering by the number of times a match occurs, but is biased towards longer strings with more matches.