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

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.

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.

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.

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')