ordering by match accuracy in t-sql using multiple where-like statements

I am building a query such as this:

SELECT * FROM mytable 
WHERE field like '%a%'
OR field like '%b%'
OR field like '%c'

I'd开发者_运维问答 like to tag on an ORDER BY clause that orders by the amount of matches found--something along the lines of making some extra column in the result set that increments if a, b or c was found in the field and then order by that number.

is this possible without creating some kind of temp table?

---

You probably want to look into full text indexing if this table is at all large but a query without.

SELECT *
FROM   mytable
WHERE  field like '%a%'
        OR field like '%b%'
        OR field like '%c'
ORDER  BY CASE
            WHEN field like '%a%' THEN 1
            ELSE 0
          END + CASE
                  WHEN field like '%b%' THEN 1
                  ELSE 0
                END + CASE
                        WHEN field like '%c%' THEN 1
                        ELSE 0
                      END