Built-In Functions Tutorial

This tutorial shows in a simple Cω program how to use functions for working with various types of data that are built into Microsoft SQL Server 2000.

Sample Files

To run this tutorial, you may use the following project and source files:

• Functions.cwproj
• main.cw
• Functions.exe.config

These files are located in the \samples\SQL\Functions subdirectory under the path where you installed Cω, which by default is C:\Program Files\Microsoft Research\Comega.

To run this sample, you need to have a SQL configuration file in the same directory as the Select.exe you make in Visual Studio. Here are the contents of the version of this file that Cω provided you as a starting point:

<configuration>
  <appSettings>
    <add key="Northwind" value="Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI" />
  </appSettings>
</configuration>

Verify that this is the correct SQL connection string for connecting to the Northwind database on your SQL Server 2000 installation. If necessary, you can modify the value attribute in the <add> element

When you have verified it is correct, press F5 in Visual Studio to make and run the sample.

Further Reading

• In Microsoft SQL Server Books Online, see "Functions" within the Transact-SQL Reference.

Tutorial

SQL built-in functions are provided for compatiblity with common SQL expressions. The following are functions for working with various types of data that are provided by Microsoft SQL Server.

Math functions

• Abs(), Acos(), Asin(), Atan(), Atan2(), Ceiling(), Cos(), Degrees(), Exp(), Floor(), Log(), Log10(), Power(), Round(), Sin()

String functions

• CharIndex(), Left(), Len(), Replace(), Reverse(), Right(), Stuff(), Substring()

Date/Time functions

• DateAdd(), DatePart(), GetDate(), GetUtcDate(), IsDate()

For a complete list of all SQL built-in functions, refer to Microsoft SQL Server Books Online.

Example

The following is a complete Cω program that demonstrates how to use some of the SQL built-in functions.

using System;
using System.Query;
using System.Data.SqlTypes;
using Northwind;

public class Test {
  static void Main() {
     Console.WriteLine("SQL Functions");
     Console.WriteLine("-------------");

     // SQL functions can be used within select, insert, update and delete commands
     Database db = new Database();

     // Use SQL functions in select list
     SqlMoney freight = select singleton Ceiling(Freight) from db.Orders where OrderID == 10643;
     Console.WriteLine("Rounded freight charge = {0}", freight);

     // Use SQL functions in the where clause
     SqlString id = select singleton CustomerID from db.Customers where Right(CustomerID, 2) == "KI";
     Console.WriteLine("CustomerID ending with KI = {0}", id);

     // Use SQL functions in order by, group by and having clauses too
     Console.Write("CustomerID groups: ");
     foreach (row in select top 6 prefix from db.Customers group by Left(CustomerID,2) as prefix order by prefix) {
       Console.Write("{0} ", row.prefix);
     }
     Console.WriteLine();

     // USE SQL functions everywhere
     Console.WriteLine("Substring(7,5) = {0}", Substring("Hello World", 7, 5));
     Console.WriteLine("Reverse() = {0}", Reverse("Hello World"));
     Console.WriteLine("Abs(-1.23) = {0}", Abs(-1.23));
     Console.WriteLine("Floor(1.23) = {0}", Floor(1.23));
     SqlDateTime dt = DateTime.Parse("2/3/2003");
     Console.WriteLine("Day={0}, Month={1}, Year={2}", DatePart(Day, dt), DatePart(Month, dt), DatePart(Year, dt));

     Console.Write("\nPress ENTER to continue...");
     Console.ReadLine();
  }
}

Output

SQL Functions
-------------
Rounded freight charge = 30.0000
CustomerID ending with KI = ALFKI
CustomerID groups: AL AN AR BE BL BO
Substring(7,5) = World
Reverse() = dlroW olleH
Abs(-1.23) = 1.23
Floor(1.23) = 1
Day=3, Month=2, Year=2003

Press ENTER to continue...