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

user_360838 0
Viewed 14012

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

sql-order-by tsql sql-server mysql sql
4 Answers

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
user_870150 
ORDER BY CHARINDEX(','+convert(varchar,status)+',' ,   
  ',rejected,active,submitted,approved,')


just put a comma before and after a string in which you are finding the substring index or you can say that second parameter.

and first parameter of charindex is also surrounded by ,
user_135165540
Related