SQL Server Functions

With version 7.0, Microsoft added more than 30 functions to an already large number of functions. More were added in SQL Server 2000 as well to support new features or capabilities, or to provide additional functionality.

Some of the SQL Server functions provide shortcuts to obtain information that could be retrieved in other ways. For instance, one function will obtain an object ID (OBJECT_ID()) if you know the object's name, but looking it up in the sysobjects table could also work.

Other functions, such as some of the mathematical functions, are more essential. (Okay, it could be argued that you can calculate the square root, for instance, using T-SQL code, but it is not efficient.)

Most functions have the following structure:

FUNCTION_NAME([parameter1 [, parameter2 [, ...]]) 

The parameters might be an expression (such as a column name or another function), a constant, or a special code (such as a formatting code).

A function returns a value. The datatype for the value depends on the function you are using. Take a look at the available functions grouped by category.

String Functions

The string functions allow you to perform concatenation, parsing manipulation, and so on with strings.

Table 26.3 lists the available string functions. They can be used against any string expression.

You can use the operator + to concatenate strings.

The following example uses SUBSTRING and string concatenation to present each author's first letter of the first name and then the last name:

SELECT SUBSTRING(au_fname,1,1) + '. ' + au_lname FROM authors 

Mathematical Functions

The mathematical functions in Table 26.4 perform calculations based on the input values and return a numeric value. No new mathematical functions were introduced in version 7.0.

The operators +, -, *, /, and % (modulo) are also available for numeric expressions.

Date Functions

The date functions perform operations such as formatting and subtracting. The expression given is a datetime datatype.

Some of the functions take a datepart as argument. The datepart specifies on what part of the datetime datatype you want to operate. Table 26.5 provides the codes for the datepart.

The date- and time-related functions are listed in Table 26.6.

You can use the operators + and - directly on datetime expressions in version 7.0 and later. The implied datepart is days. In this example, you use the + operator to add one day to the current date:

SELECT GETDATE(), GETDATE() + 1 
go

----------------------- ----------------------
1998-03-28 16:08:33     1998-03-29 16:08:33

Metadata Functions

The metadata functions, listed in Table 26.7, are useful for retrieving information such as column names, table names, index keys, and so on. Basically, many of the functions are shortcuts for querying the system tables.

System Functions

The system functions, listed in Table 26.8, are useful for retrieving information about values, options, and settings within the SQL Server.

The following example returns the title ID and price for all books. If the price is not set (NULL), it returns a price of 0:

SELECT title_id, ISNULL(price, 0) FROM titles 

Let us expand the example. You want to display the string 'Not Priced' for those that contain NULL values. You have to convert the price to a character value before replacing NULL with your text string:

SELECT title_id, ISNULL(CONVERT(CHAR(10),price), 'Not Priced') 
FROM titles

Security Functions

The security functions, listed in Table 26.9, are useful for retrieving information about users and roles.

The following example returns the current user's database username, login name, and whether the user is a member of the db_owner role:

select substring (user_name(), 1, 20) AS 'user_name', 
       substring (suser_sname(),1, 30) AS 'login_name',
       case IS_MEMBER('db_owner')
          when 1 then 'Yes'
          else 'No'
          end AS 'Is_dbOwner'
go

user_name            login_name                     Is_dbOwner
-------------------- ------------------------------ ----------
dbo                  RRANKINSA20P\rrankins          Yes

Text and Image Functions

These scalar functions, listed in Table 26.10, perform an operation on a text or image input value or column and return information about the value.

The following example shows how to use the TEXTPTR function to retrieve a text pointer and use it in the READTEXT command:

USE pubs 
GO
DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(pr_info)
   FROM pub_info
   WHERE pub_id = '0877'
READTEXT pub_info.pr_info @ptr 0 65
GO

pr_info
-----------------------------------------------------------------
This is sample text data for Binnet & Hardley, publisher 0877 in

Rowset Functions

The rowset functions listed in Table 26.11 return an object that can be used in place of a table reference in a Transact-SQL statement.

The following example uses the OPENROWSET function and the Microsoft OLE DB Provider for SQL Server to titles records from the titles table in the pubs database on a remote server named RRANKINSA20P. Notice how the result from the function can be used just like a table, even in a JOIN clause:

select p.pub_id, t.title 
   from publishers p
   join OPENROWSET('SQLOLEDB','RRANKINSA20P';'sa';'',
                     'SELECT * FROM pubs.dbo.titles ORDER BY pub_id') AS t
     on p.pub_id = t.pub_id
go

pub_id title
------ ------------------------------------------------------------------------
0736   You Can Combat Computer Stress!
0736   Is Anger the Enemy?
0736   Life Without Fear
0736   Prolonged Data Deprivation: Four Case Studies
0736   Emotional Security: A New Algorithm
0877   Silicon Valley Gastronomic Treats
0877   The Gourmet Microwave
0877   The Psychology of Computer Cooking
0877   Computer Phobic AND Non-Phobic Individuals: Behavior Variations
0877   Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
0877   Fifty Years in Buckingham Palace Kitchens
0877   Sushi, Anyone?
1389   The Busy Executive's Database Guide
1389   Cooking with Computers: Surreptitious Balance Sheets
1389   Straight Talk About Computers
1389   But Is It User Friendly?
1389   Secrets of Silicon Valley
1389   Net Etiquette

To learn more about the CONTAINSTABLE and FREETEXTTABLE functions, see Chapter 44, "SQL Server Full-Text Search Services." For more information on using XML documents and the OPENXML function, see Chapter 41, "Using XML in SQL Server 2000."

Niladic Functions

The niladic group of functions, list