Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 368
Dash Avatar asked Oct 08 '10 16:10

Dash


People also ask

Is Microsoft SQL Server similar to MySQL?

Both MySQL and Microsoft SQL Server (MSSQL) are widely used enterprise database systems. MySQL is an open-source relational database management system (RDBMS), while MSSQL Server is a Microsoft-developed RDBMS. Enterprises can choose between multiple MSSQL Server editions to suit their individual needs and budgets.

What is difference between SQL MSSQL Server and MySQL?

SQL is a query programming language that manages RDBMS. MySQL is a relational database management system that uses SQL. SQL is primarily used to query and operate database systems. MySQL allows you to handle, store, modify and delete data and store data in an organized way.

What is field function in MySQL?

MySQL FIELD() Function The FIELD() function returns the index position of a value in a list of values. This function performs a case-insensitive search. Note: If the specified value is not found in the list of values, this function will return 0. If value is NULL, this function will return 0.

What is MySQL SQL Server MS Access?

SQL is an acronym for Structured Query Language. It is used to access, manipulate and retrieve information from a database. MySQL is an open source Relational Database Management System (RDBMS) based on Structured Query Language (SQL). It runs on platforms like Linux, UNIX and Windows.


3 Answers

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
like image 104
OMG Ponies Avatar answered Oct 20 '22 02:10

OMG Ponies


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
like image 33
Salman A Avatar answered Oct 20 '22 03:10

Salman A


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

like image 38
Jason Avatar answered Oct 20 '22 02:10

Jason