Native Functions | Mastering Dreamweaver MX Databases

An RDBMS provides native (built-in) functionality in the form of functions that perform a predefined calculation or task. Generally, you provide a reference to values or information that the function processes within the confines of its defined job. You can use SQL Server functions in the following:

A SELECT statement's field list to return a value
A SQL WHERE clause search condition (SELECT, INSERT, DELETE, or UPDATE) to limit the returned rows
Any expression
A CHECK constraint or trigger to check for specified values when inserting data
A DEFAULT constraint when inserting data

Note

A SQL Server function is either deterministic or nondeterministic. Deterministic functions always return the same result. DateAdd is a deterministic value because it returns the same result for the fixed set of input values. Nondeterministic functions return different results each time they are called, depending on the input values. For instance, GetDate returns the current date, which will always be a different value every time it's called (because the result also includes a time value). Only deterministic functions can be invoked in views and computed columns.

There are several types of SQL Server functions. In this next section, we review Date and Time, Mathematical, System, and String functions. This list isn't comprehensive by any means, but it does include the functions you'll probably use most often.

Note

Aggregate functions perform operations on groups of records. You can't limit the set based on criteria. The function processes the calculation on all the values in a single column. These functions are actually Transact-SQL functions and as such aren't truly native to SQL Server. You'll find more information on these functions in the "Using Transact-SQL in SQL Server" section later in this chapter.

Date and Time Functions

Date and Time functions use date arithmetic or serial values in their calculations. SQL Server supports dates from January 1, 1753, through December 31, 9999. Oddly enough, December 30, 1899, is considered the anchor date (instead of January 1, 1900, as you might expect). An anchor date has a serial value of 0. Several Date functions accept an argument that denotes a specific interval. Table 8.2 lists these constants.

Table 8.2: INTERVAL OPTIONS FOR SOME DATE FUNCTIONS
Option	Description
yyyy, yy, year	Returns a valid 4-digit year value
y	Returns a value between 1 and 365, representing the date's position within the year—between January 1 and December 31
mm, m, month	Returns the month as a value between 1 and 12
qq, q, quarter	Returns a value 1 through 4, denoting the quarter of the year
dd, d, day	Denotes day as a value between 1 and 31
dw	Denotes position of day within the week; a value between 1 and 7
ww, wk	Returns a value between 1 and 52, which represents the week of the year that the date falls within
hh, hour	Denotes hours as a value between 0 and 23
mi, n, minute	Denotes minutes as a value between 0 and 59
ss, s, second	Denotes seconds as a value between 0 and 59

GetDate()

GetDate() returns the current date and time value. The following statement returns the current date in a column named CurrentDate.

SELECT GetDate() As CurrentDate  FROM table

DatePart(datecode,date)

DatePart() returns a number of date components, and datecode is one of many options that define the time or date component. For instance, if the date field or variable contains August 10, 2002, the following returns the value 10.

SELECT DatePart(dd, date) As DayValue  FROM table

DateAdd(datecode, interval, date)

The DateAdd() function adds an interval to a specific date. For instance, if the date column contained the dates August 10, 2002, and September 10, 2002, the following statement returns September 10, 2002, and October 10, 2002—adding exactly one month to each date.

SELECT DateAdd(mm, 1, date)  FROM table

DateDiff(datecode, date, datestring)

The DateDiff() function returns the difference between date and datestring. That difference can be presented in any date component, as specified by datecode. For instance, if the date column contains the dates August 10, 2002, and September 10, 2002, and datestring is November 4, 2002, the following statement returns the values 86 and 55.

SELECT DateDiff(d, date, '11/4/2002')  FROM table

Day(date)

Day() returns a value between 1 and 31 that represents the day of the month of the specified date. The following statement returns 10 if the date in question is 8/10/2002.

SELECT Day(date) As DayValue  FROM table

Month(date)

Month() returns a value between 1 and 12, a value represents a month between January and December. The following statement returns 8 if the date in question is 8/10/2002.

SELECT Month(date) As DayValue  FROM table

Year(date)

Year() extracts the year value as a four-digit value from the specified date. The following statement returns 2002 if the date in question is 8/10/2002.

SELECT Year(date) As DayValue  FROM table

Mathematical Functions

Mathematical functions perform mathematical operations on numeric data and return the results. The user supplies the operands in the form of arguments, and the function processes those values within the context of its purpose.

Abs(value)

Abs() returns the absolute, or positive, value of a value or expression. The following statement returns the values 1, 2, and 3 if value contains the values –1, –2, and –3.

SELECT Abs(value) As AbsValue  FROM table

Ceiling(value)

Ceiling() returns the smallest integer greater than or equal to the given numeric expression. The following statement returns the value 2 if value is 1.21 or 1.63 and returns –1 if value is –1.21 or –1.63:

SELECT Ceiling(value) As GreaterThan  FROM table

Floor(value)

Floor() returns the largest integer less than or equal to the given numeric expression. The following statement returns 1 when value is 1.21 and 1.63 and returns –2 when value is –1.21 and –1.63.

SELECT Floor(value) As LessThan  FROM table

Rand(seed)

Rand() returns a random float value from 0 to 1. The following statement returns a series of random values, depending on the seed value.

SELECT Rand(seed) As RandomValue  FROM table

A query returns the same results if the seed value doesn't change.

Round(value,length,function)

Round() returns a value, rounded to the specified length, but it's flexible. You can use Round() to round or truncate a value. You can apply the following rules to round values:

If length is negative and larger than or equal to the number of digits to the left of the decimal point, Round() returns 0. The following statements return 0.
```
SELECT Round(1.23, -1) AS RoundValue  FROM table  SELECT Round(1.23, -2 AS RoundValue  FROM table  SELECT Round(12.23, -3) AS RoundValue FROM  table 
```
Round() returns a rounded value when length is a negative number. The following statements return the values 10.00 and 100.00, respectively.
```
SELECT Round(12.23, -1) AS RoundValue  FROM table  SELECT Round(123.45, -2) AS RoundValue  FROM table
```
Use the function argument (the default is 0) to truncate a value. When function is any value other than 0, the value is truncated. Generally, the length value needs to be 0 when truncating. The following statements return the values 12, 12.20, and 12.23, respectively.
```
SELECT Round(12.23, 0, 1) AS TruncatedValue  FROM table SELECT Round(12.23, 1, 1) AS  TruncatedValue  FROM table SELECT Round(12.23, 2, 1) AS  TruncatedValue  FROM table
```
Expect normal rounding behaviors when length is a positive value and function is 0 (or omitted). The following statements return the values 120, 100, and 0, respectively.
```
SELECT Round(123, -1) AS RoundedValue FROM  table SELECT Round(123, -2) AS RoundedValue FROM table  SELECT Round(123, -3) AS RoundedValue FROM table
```

System Functions

System functions operate or report on various system level options and objects by indirectly accessing information from system tables. Some functions are preceded with two at signs (@@). The following System functions return a corresponding identification value or name:

DB_ID() and DB_NAME()

The DB_ID and DB_NAME functions return a database identification value or name. The following statement returns the value 6 when Northwind is the current (active) database.

SELECT DB_ID() As DBID

The following statement returns Northwind when that database is current.

SELECT DB_NAME() As DBName

HOST_ID() and HOST_NAME()

These two functions return the host identification value or name, which will be unique to your system. Try the following to return the host identification value.

SELECT HOST_ID() As HostID

The following returns the host name, which should be the same as the current instance of SQL Server.

SELECT HOST_NAME() AS HostName

OBJECT_ID('name') and OBJECT_NAME(value)

These two functions return an object's identification value or name. To return a known object's identification value, use the following syntax:

SELECT OBJECT_ID('sysfilegroups') AS ObjectID

When you know the identification value and you want to know the object's name, use the following syntax:

SELECT OBJECT_NAME(24) AS ObjectName

USER_ID() and USER_NAME()

Use these functions to return the user identification value and name. The following statement returns the user identification value.

SELECT USER_ID() AS UserID

This statement returns the user name.

SELECT USER_NAME() AS UserName

@@Identify

Use @@Identify after inserting data to return the last-inserted identify value. For instance, the following statement inserts new values into table and then returns the latest identify value from that operation.

INSERT INTO Employees (lastname, firstname,  ssnumber) VALUES ('Smith', 'Janice', '555-55-5555')  SELECT @@IDENTITY As 'Identity'

Meta Data Functions

Meta Data functions are a type of System function; they return information about the database and database objects. There are several Meta Data functions, and all are nondeterministic. We'll review just a few of them in this section.

COL_LENGTH('table','column')

This function returns the length of a specific column. The following statement returns the value 4—the length of the OrderID column in the Northwind Orders table.

USE Northwind SELECT COL_LENGTH('Orders',' OrderID')

COL_NAME('tableid','columnid')

This function returns a column's name. The following statement returns the column name EmployeeID.

USE Northwind SELECT COL_NAME(OBJECT_ID ('Employees'),1)

Rowset Functions

Rowset functions return an object that can be used in a Transact-SQL statement in place of a table reference. They're all nondeterministic.

OpenDataSource(providername,initalizestring)

The OpenDataSource() function provides connection information without using a linked server name. The providername argument is the registered name of the PRODIG of the OLE DB provider. Use any of the keywords in Table 8.3 as the initializestring argument.

Table 8.3: *OPENDATASOURCE* KEYWORDS
Keyword	OLE DB Property	Valid Values and Description
Data Source	DBPROP_INIT_DATASOURCE	Name of the data source (connection). Providers interpret this in different ways. For a SQL Server OLE DB provider, this indicates the name of the server. For a Jet OLE DB provider, this indicates the full path of the MDB file or XLS file.
Location	DBPROP_INIT_LOCATION	Location of the database (connection).
Extended Properties	DBPROP_INIT_PROVIDERSTRING	The provider-specific connect-string.
Connect Timeout	DBPROP_INIT_TIMEOUT	Time-out value after which the connection attempt fails.
User ID	DBPROP_AUTH_USERID	User's identification value.
Password	DBPROP_AUTH_PASSWORD	Password to get past security.
Catalog	DBPROP_INIT_CATALOG	The name of the initial or default catalog when connecting to the data source.

Use this function in the following form:

SELECT *FROM OPENDATASOURCE('SQLOLEDB','Data Source=servername;    UserID=userid;Password=password'). database

String Functions

String functions perform various operations on character and binary string values. Most can be used only with Char, NChar, VarChar, and NVarChar datatypes. When passing a literal string to the function, you must usually enclose the string in single-quotation delimiters in the form 'string'.

Substring(searchstring, begin, length)

Substring() retrieves a portion of a string. For example, the following statement returns the string 'bcde'—it begins to extract characters at the second letter in the specified string and extracts four characters.

SELECT Substring('abcdef', 2, 4)

Soundex() and Difference()

These two functions search for similar sounds in a character string. Soundex() converts a character string to a 4-digit code. The function ignores vowels and terminates when encountering a non- alphabetic character. Both strings in the following statement return the value S25.

SELECT Soundex('Susan'), Soundex('Suzanne')

The two strings, 'boy' and 'toy', return the values B000 and T000. (Those are zeros, not upper- case O's.)

SELECT Soundex('boy'), Soundex('toy')

The Difference() function compares the results of running the Soundex() function on the specified strings and then returns a value from 0 through 4, 4 being the best match. The two sets of strings from the Soundex() example return the values 4 and 3, respectively—implying that Susan and Suzanne are more similar than boy and toy:

SELECT Difference('Susan', 'Suzanne')  SELECT Difference('boy', 'toy')

Stuff(string, begin, length, substring)

The Stuff() function replaces characters in one string with another, beginning at a specified position within the target string and replacing existing characters with a specified number of characters from the substring. The following statement replaces the third character with the letter o to return the word block.

SELECT Stuff('black', 3, 1, 'o')

Note

This section is by no means comprehensive. You can find more information on SQL Server functions at http:// msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_9rar.asp or in Books Online, which comes with SQL Server 2000.