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