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

Question

MySQL provides a string function named FIELD() which accepts a variable number of arguments. The return value is the location of the first argument in the list of the remaining ones. In other words:

FIELD('d', 'a', 'b', 'c', 'd', 'e', 'f')

would return 4 since 'd' is the fourth argument following the first.

This function provides the capability to sort a query's results based on a very specific ordering. For my current application there are four statuses that I need to manager: active, approved, rejected, and submitted. However, if I simply order by the status column, I feel the usability of the resulting list is lessened since rejected and active status items are more important than submitted and approved ones.

In MySQL I could do this:

SELECT <stuff> FROM <table> WHERE <conditions> ORDER BY FIELD(status, 'rejected', 'active','submitted', 'approved')

and the results would be ordered such that rejected items were first, followed by active ones, and so on. Thus, the results were ordered in decreasing levels of importance to the visitor.

I could create a separate table which enumerates this importance level for the statuses and then order the query by that in descending order, but this has come up for me a few times since switching to MS SQL Server so I thought I'd inquire as to whether or not I could avoid the extra table and the somewhat more complex queries using a built-in function similar to MySQL's FIELD().

Thank you,
David Kees

Solution 1

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

Solution 2

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

Solution 3

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

Comments

[Salman A]

There is an identical answer 2 years older than yours.