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


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


Use a CASE expression (SQL Server 2005+):

           WHEN 'active' THEN 1
           WHEN 'approved' THEN 2
           WHEN 'rejected' THEN 3
           WHEN 'submitted' THEN 4
           ELSE 5

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

           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

Answered By – OMG Ponies

Answer Checked By – Katrina (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.