What Is the Ms SQL Server Capability Similar to the MySQL Field() Function

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

Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?

You could use the BIT datatype to represent boolean data. A BIT field's value is either 1, 0, or null.

LIMIT style functionality in MS SQL Server 2005

SQL Server's equivalent to MySQL/PostgreSQL's LIMIT syntax is TOP (SQL Server 2000+), but TOP doesn't support the offset value...

Assuming SQL Server 2005+, use:

SELECT x.*
  FROM (SELECT t.*,
               ROW_NUMBER() OVER (ORDER BY ?) AS rank
          FROM BLAH t) x
 WHERE x.rank BETWEEN 6 AND 20

Mind that you have to define a sort order for the ranking - replace the "?" with the appropriate column(s).

Is there a combination of LIKE and IN in SQL?

There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.

Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:

Oracle:

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0

SQL Server:

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

The column you are querying must be full-text indexed.

Reference:

• Building Full-Text Search Applications with Oracle Text
• Understanding SQL Server Full-Text

Why is there no PRODUCT aggregate function in SQL?

There is no PRODUCT set function in the SQL Standard. It would appear to be a worthy candidate, though (unlike, say, a CONCATENATE set function: it's not a good fit for SQL e.g. the resulting data type would involve multivalues and pose a problem as regards first normal form).

The SQL Standards aim to consolidate functionality across SQL products circa 1990 and to provide 'thought leadership' on future development. In short, they document what SQL does and what SQL should do. The absence of PRODUCT set function suggests that in 1990 no vendor though it worthy of inclusion and there has been no academic interest in introducing it into the Standard.

Of course, vendors always have sought to add their own functionality, these days usually as extentions to Standards rather than tangentally. I don't recall seeing a PRODUCT set function (or even demand for one) in any of the SQL products I've used.

In any case, the work around is fairly simple using log and exp scalar functions (and logic to handle negatives) with the SUM set function; see @gbn's answer for some sample code. I've never needed to do this in a business application, though.

In conclusion, my best guess is that there is no demand from SQL end users for a PRODUCT set function; further, that anyone with an academic interest would probably find the workaround acceptable (i.e. would not value the syntactic sugar a PRODUCT set function would provide).

Out of interest, there is indeed demand in SQL Server Land for new set functions but for those of the window function variety (and Standard SQL, too). For more details, including how to get involved in further driving demand, see Itzik Ben-Gan's blog.

MySQL ordering in Select .. In statement

You can use the FIELD function:

select id from user where id IN (2, 3, 1) ORDER BY FIELD(id, 2, 3, 1);

SQL search multiple values in same field

Yes, you can use SQL IN operator to search multiple absolute values:

SELECT name FROM products WHERE name IN ( 'Value1', 'Value2', ... );

If you want to use LIKE you will need to use OR instead:

SELECT name FROM products WHERE name LIKE '%Value1' OR name LIKE '%Value2';

Using AND (as you tried) requires ALL conditions to be true, using OR requires at least one to be true.

---

Related Topics