Home

	Expand Search

Transact-SQL User's Guide

Chapter 10 Using the Built-In Functions in Queries

System functions that return database information

Examples of using system functions

Examples of using system functions

The examples in this section use these system functions:

• col_length

• datalength

• isnull

• user_name

This query finds the length of the title column in the titles table (the "x=" is included so that the result has a column heading):

select x = col_length("titles", "title") 

 x 
-------- 
      80 
 
 (1 row affected) 

In contrast to col_length, which finds the defined length of a column, datalength reports the actual length, in bytes, of the data stored in each row. Use this function on varchar, nvarchar, varbinary, text, and image datatypes, since they can store variable lengths and do not store trailing blanks. datalength of any NULL data returns NULL. When a char value is declared to allow NULLS, Adaptive Server stores it internally as a varchar. All other datatypes report their defined length. Here is an example that finds the length of the pub_name column in the publishers table:

select Length = datalength(pub_name), pub_name 
from publishers 

Length  pub_name
------  ------------------------ 
13      New Age Books
16      Binnet & Hardley
20      Algodata Infosystems
 
(3 rows affected)

This query finds the average of the prices of all titles, substituting the value "$10.00'' for all NULL entries in price:

select avg(isnull(price,$10.00)) 
from titles

------------
       14.24
 
(1 row affected)

This query finds the row in sysusers where the name is equal to the result of applying the system function user_name to user ID 1:

select name 
from sysusers 
where name = user_name(1) 

name
------------------------ 
dbo
 
(1 row affected)