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

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

For your particular example your could:

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

Note that FIELD was 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.

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', 1UNION SELECT 'approved', 2UNION SELECT 'rejected', 3UNION SELECT 'submitted', 4)SELECT <field1>, <field2>FROM <table> tblINNER JOIN cte ON cte.status = tbl.status  -- do the joinORDER BY cte.RN  -- use the ordering defined in the cte

Good luck,

Jason