What is the MS SQL Server capability similar to the MySQL FIELD() function?

I recommend a CTE (SQL server 2005+). No need to repeat the status codes or create the separate table.

WITH cte(status, RN) AS (  -- CTE to create ordered list and define where clause
      SELECT 'active', 1
UNION SELECT 'approved', 2
UNION SELECT 'rejected', 3
UNION SELECT 'submitted', 4
)
SELECT <field1>, <field2>
FROM <table> tbl
INNER JOIN cte ON cte.status = tbl.status  -- do the join
ORDER BY cte.RN  -- use the ordering defined in the cte

Good luck,

Jason

For your particular example your could:

ORDER BY CHARINDEX(
    ',' + status + ',',
    ',rejected,active,submitted,approved,'
)

Note that FIELD is supposed to return 0, 1, 2, 3, 4 where as the above will return 0, 1, 10, 17 and 27 so this trick is only useful inside the order by clause.

---

A set based approach would be to outer join with a table-valued-constructor:

LEFT JOIN (VALUES
    ('rejected',  1),
    ('active',    2),
    ('submitted', 3),
    ('approved',  4)
) AS lu(status, sort_order)
...
ORDER BY lu.sort_order

Use a CASE expression (SQL Server 2005+):

ORDER BY CASE status
           WHEN 'active' THEN 1
           WHEN 'approved' THEN 2
           WHEN 'rejected' THEN 3
           WHEN 'submitted' THEN 4
           ELSE 5
         END

You can use this syntax for more complex evaluation (including combinations, or if you need to use LIKE)

ORDER BY CASE 
           WHEN status LIKE 'active' THEN 1
           WHEN status LIKE 'approved' THEN 2
           WHEN status LIKE 'rejected' THEN 3
           WHEN status LIKE 'submitted' THEN 4
           ELSE 5
         END