Types of Functions
Based on the type of result that is returned, there are two primary groups of built-in functions:
-
Scalar
-
Rowset
Scalar Functions
Most of the time when we refer to functions, we are thinking of the scalar type. The name of this type refers to the fact that these functions return only one value.
Based on their functionality (although not necessarily their return values), we can divide scalar functions into the following groups:
-
System
-
Date and time
-
String
-
Mathematical
-
Metadata
-
Security
-
Cryptographic
-
Text and image
-
Cursor
-
Configuration
-
System statistical
-
Aggregate
-
Ranking
We will not be able to cover in detail all the built-in functions available in SQL Server, but we will discuss the key functions that you will use most frequently. You can find complete documentation of all built-in functions in SQL Server Books OnLine.
System Functions
System functions return information related to the Microsoft SQL Server environment. They are used to return object names and identifiers, the current user, the current database, session, application, and login; to investigate the data type of an expression; and to perform conversions between data types.
Let's examine some of the system functions that are likely to be more frequently used and look at some examples.
Conditional Expression: Case In other programming languages, Case is considered to be a flow-control programming construct. In earlier versions of SQL Server documentation, Case was classified as an expression. Since SQL Server 7.0, it is classified as a function, which is mathematically more correct. However, all of these classifications are more or less true.
The Case function/expression enables the user to evaluate an expression and to return the value associated with the result of the expression. For example, the Case function/expression in the following stored procedure returns the approximate number of days associated with a leasing schedule:
Create Procedure dbo.ap_LeasePeriodDuration_Get -- return approximate number of days associated with lease frequency @inyScheduleFrequencyId tinyint, @insDays smallint OUTPUT As Declare @chvScheduleFrequency varchar(50) Select @chvScheduleFrequency = ScheduleFrequency From dbo.ScheduleFrequency where ScheduleFrequencyId = @inyScheduleFrequencyId select @insDays = Case @chvScheduleFrequency When 'monthly' then 30 When 'semi-monthly' then 15 When 'bi-weekly' then 14 When 'weekly' then 7 When 'quarterly' then 92 When 'yearly' then 365 END return
The Case function/expression works much like a Select statement with nested If statements. In fact, most of the time, you can write equivalent code using nested If statements.
There are two types of Case functions/expressions:
-
Simple Case functions/expressions
-
Searched Case functions/expressions
A simple Case function/expression has the following syntax:
Case input_expression WHEN when_ expression THEN result_expression [...n] [ ELSE else_ result_expression ] END
The previous example used this kind of Case function/expression. SQL Server attempts to match the input_expression with one of the when_expressions. If it is successful, it returns the result_expression associated with the first matching when_ expression. An Else clause is also part of the Case function/expression. If the value of the input_expression is not equal to either of the when_expressions, the function returns the value of the else_result_expression.
A searched Case function/expression is very similar. The only difference is that it does not have an input_expression. The complete criteria are inside the When clause in the form of a Boolean expression:
Case WHEN Boolean_expression THEN result_expression [...n] [ ELSE else_result_expression ] END
SQL Server returns the result_expression associated with the first Boolean_ expression, which is true. If all Boolean_expressions are false, SQL Server returns the else_result_expression.
In the following example, a searched Case function/expression has to be used because the Boolean_expressions have different operators (= and Like):
Create Procedure dbo.ap_LeaseInfo_List -- list all lease contract information As Select LeaseVendor [Lease Vendor], LeaseNumber [Lease Number], Case -- some vendors have id of sales reps -- incorporated in lease numbers When LeaseVendor = 'Trigon FS' Then Substring(LeaseNumber, 5, 12) When LeaseVendor Like 'EB%' Then Substring(LeaseNumber, 9, 8) When LeaseVendor Like 'MMEX%' Then Substring(LeaseNumber, 7, 6) When LeaseVendor = 'DAFS' Then Substring(LeaseNumber, 8, 11) Else 'Unknown' end [Lease Agent], ContractDate [Contract Date] from dbo.Lease
| Tip | Although both examples use Case functions/expressions as a part of the Select statement, keep in mind that you can use them anywhere that you can place an expression. This flexibility might come in very handy in some situations. |
Getting Information About Data You can use numerous functions to return information about expressions, the most important of which are the following:
-
IsDate()
-
IsNumeric()
-
DataLength()
-
Binary_CheckSum()
IsDate() is a function that is used to determine whether an expression is a valid date. It is particularly useful when you need to read data from text files. If the result of this function is 1 (true), SQL Server guarantees that you will be able to convert the data to the datetime data type. IsDate() uses the following syntax:
IsDate(expression)
In the following stored procedure, SQL Server verifies that Lease Data (received as a string) can be converted to a datetime value. It then stores this result with the rest of the parameters in the Lease table.
Create Procedure dbo.ap_LeaseContract_Load -- insert lease contract information and return id of lease @chvLeaseVendor varchar(50), @chvLeaseNumber varchar(50), @chvLeaseDate varchar(50), @intLeaseId int OUTPUT As Declare @intError int -- test validity of date if IsDate(@chvLeaseDate) = 0 begin Raiserror ('Unable to Convert to date.', 16, 1) return -1 end insert into dbo.Lease(LeaseVendor, LeaseNumber, ContractDate) values (@chvLeaseVendor, @chvLeaseNumber, Convert(smalldatetime, @chvLeaseDate)) select @intLeaseId = Scope_Identity() return You can use the IsNumeric() function to determine whether it is possible to convert a character value or expression into one of the numeric data types (int, smallint, tinyint, real, float, money, smallmoney, decimal, or numeric). IsNumeric() uses the following syntax:
IsNumeric(expression)
The DataLength() function returns the number of bytes used to store or display an expression. This information is particularly useful when processing variable-length character data types.
| Note | DataLength() returns the number of bytes used to store the expression, not the number of characters, as does the Len() function. For example, each character in the nvarchar data type (or any of the Unicode data types) uses 2 bytes. |
The DataLength() function uses the following syntax:
DataLength(expression)
If you assign a string value to a variable and that value is too long, SQL Server will not report an error. It will simply truncate the value and assign it. You can use this function to prevent such behavior:
If DataLength(@Select) + DataLength(@From) + DataLength(@Where) <= 8000 Select @SQL = @Select + @From + @Where d
This example was critical on SQL Server 2000 and SQL Server 7. On SQL Server 2005 it is possible to define string variables using varchar (max) or nvarchar (max).
SQL Server 2000 introduced the Binary_CheckSum() function, which calculates the binary checksum of a specified expression or set of table columns. It is designed to detect changes in a record. This function uses the following syntax:
Binary_CheckSum(* expression[,...n])
| Note | Binary_CheckSum() is a much-needed tool for data warehousing projects. It allows DBAs to detect and handle the problem of "slowly changing dimensions" types 2 and 3. |
The following stored procedure compares the binary checksum of columns containing new information with the checksum of columns already stored in the table; if the values do not match, the new data will be inserted into the table:
CREATE Procedure ap_Equipment_Update -- Check if values were changed in the meantime -- Update values in equipment table. @intEqId int, @chvMake varchar(50), @chvModel varchar(50), @intEqTypeId int, @debug int = 0, As declare @intNewEqBC int set @intNewEqBC = Binary_CheckSum(@chvMake, @chvModel, @intEqTypeId) if @debug <> 0 Select @intNewEqBC NewBC if @debug <> 0 select EqBC OldBC from EquipmentBC where EqId = @intEqId) if not exists (Select EqBC from EquipmentBC where EqId = @intEqId) insert EquipmentBC (EqId, EqBC) select @intEqId, Binary_CheckSum(Make, Model, EqTypeId) from Equipment where EqId = @intEqId -- Check if values were changed in the meanwhile if @intNewEqBC <> (Select EqBC from EquipmentBC where EqId = @intEqId) begin if @debug <> 0 select 'Information will be updated.' -- update information update Equipment Set Make = @chvMake, Model = @chvModel, EqTypeId = @intEqTypeId where EqId = @intEqId if exists(select EqId from EquipmentBC where EqId = @intEqId) update EquipmentBC Set EqBC = @intNewEqBC where EqId = @intEqId else insert EquipmentBC (EqId, EqBC) values (@intEqId, @intNewEqBC) end return
Binary_CheckSum() is case-sensitive. It evaluates columns/expressions differently depending on the case (uppercase/lowercase) used in the column or expression. This might seem unusual since most SQL Server behavior depends on the code page that you select during installation. If the default is selected, SQL Server ignores the case of characters when matching them. The nature of the algorithm used to implement the Binary_CheckSum() function is such that it cannot work that way. Fortunately, SQL Server contains a CheckSum() function that ignores the case of the characters in arguments.
These functions are also valuable for comparing and indexing long string columns. Instead of creating an index on such column, you can add a column based on the checksum or binary checksum of the value in the string column and index it instead. This type of index is typically called a hash index. SQL Server will find matching records by comparing int values instead of comparing long strings, which might provide significant performance improvement.
Functions for Handling null Values SQL Server is equipped with a set of three functions to help ease the pain of using null in your database system:
NullIf(expression, expression) IsNull(check_expression, replacement_value) Coalesce(expression [,...n])
NullIf() returns null if two expressions in the function are the same value. If the expressions are not equivalent, the function returns the value of the first expression.
This function can be useful when calculating the average of columns that accept null values. For example, let's assume that the author of the Asset database has created constraints or stored procedures such that a user can leave the value of the Inventory Rent column as either null or zero when equipment is not leased. In this case, the Avg() function for calculating the average of the column will eliminate records containing null from the average but keep records with zero. It is not that the Avg() function is implemented improperly, but rather that our design can be improved. It is possible to implement a workaround using the NullIf() function:
select AVG(Rent) [average without nulls], AVG(NullIf(Rent, 0)) [average without nulls and zeros] from Inventory
An average calculated in this way will be different from an average calculated in the standard way:
average without nulls average without nulls and zeros --------------------- ------------------------------- 100.0000 150.0000 (1 row(s) affected) Warning: Null value eliminated from aggregate.
The IsNull() function examines the check_expression. If its value is null, the function returns the replacement_value. If the value of the check_expression is not null, the function returns the check_expression.
Let's suppose you want to calculate an average based on the total number of computers in the Inventory table. You can use the IsNull() value to replace null values during the calculation:
select AVG(Rent) [Eliminating nulls], AVG(ISNULL(rent, 0)) [with nulls as zeros] from Inventory
The average price of computers which counts nulls as zeros is less than the average that ignores computers with the price set to null:
Eliminating nulls with nulls as zeros ------------------ --------------------- 100.0000 75.0000 (1 row(s) affected) Warning: Null value eliminated from aggregate.
The last line is a warning that refers to the fact that null values are excluded when Avg() is calculated.
| Note | The name of this function is confusing, especially if you are a Visual Basic programmer. It cannot he used to test whether the value of an expression is null. You should use these operators instead: |
If expression IS null If expression IS NOT null
The Coalesce() function is often used to coalesce (unite) values that are split into several columns. The result of the function is the first non-null expression. This function uses the following syntax:
COALESCE(expression [,...n])
In the following example, we coalesce values from three columns (Rent, Lease, and Cost) into one value (Acquisition Cost). Coalesce() evaluates the input expressions and returns the first non-null value.
SELECT Inventory.Inventoryid, Equipment.Make + ' ' + Equipment.Model Equipment, AcquisitionType.AcquisitionType, COALESCE(Inventory.Rent, Inventory.Lease, Inventory.Cost) [Cost] FROM Inventory INNER JOIN AcquisitionType ON Inventory.AcquisitionTypeID = AcquisitionType.AcquisitionTypeID INNER JOIN Equipment ON Inventory.EquipmentId = Equipment.EquipmentId
The result contains just one column, showing the cost of acquisition:
Inventoryid Equipment AcquisitionType Cost ----------- ------------------------- -------------- --------- 5 Toshiba Portege 7020CT Purchase 1295.0000 6 Toshiba Portege 7020CT Rent 200.0000 8 Toshiba Portege 7020CT Lease 87.7500 10 Toshiba Portege 7020CT Lease 99.9500
Conversion Functions The Cast() and Convert() functions are used to explicitly convert the information in one data type to another specified data type. There is just one small difference between these two functions: Convert() allows you to specify the format of the result, whereas Cast() does not. Their syntax is
Cast (expression AS data_type) Convert(data_type [(length)], expression [, style])
In this case, expression is any value or expression that you want to convert, and data_type is the new data type. The following statement concatenates two strings and an error number and returns them as a string:
Select "Error ["+Cast(OOError as varchar)+"] has occurred."
The result is an error number integrated with a sentence, which might be useful in an error handling situation:
------------------------------------------------------- Error [373] has occurred.
In the Convert() function, style refers to the formatting style used in the conversion of date and time (datetime, smalldatetime) or numeric (money, smallmoney, float, real) expressions to strings (varchar, char, nvarchar, nchar). The following command displays the current date in the default and German style:
Select GetDate() standard, Convert(varchar, GetDate(), 104) German
The result is
standard German ----------------------------- ----------------------------- 2003-07-11 11:45:57.730 11.07.2003
Table 4-1 lists formatting styles that you can use when converting datetime to character or character to datetime information.
| Style with Two-digit Year | Style with Four-digit Year | Standard | Format |
|---|---|---|---|
| — | 0 or 100 | Default | mon dd yyyy hh:miAM (or PM) |
| 1 | 101 | USA | mm/dd/yy |
| 2 | 102 | ANSI | yy/mm/dd |
| 3 | 103 | British/French | dd/mm/yy |
| 4 | 104 | German | dd/mm/yy |
| 5 | 105 | Italian | dd-mm-yy |
| 6 | 106 | — | dd mon yy |
| 7 | 107 | — | mon dd, yy |
| 8 | 108 | — | hh:mm:ss |
| — | 9 or 109 | Default + milliseconds | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 10 | 110 | USA | mm-dd-yy |
| 11 | 111 | Japan | yy/mm/dd |
| 12 | 112 | ISO | yymmdd |
| — | 13orll3 | Europe default + milliseconds | dd mon yyyy hh:mm:ss:mmm(24h) |
| 14 | 114 | — | hh:mi:ss:mmm(24h) |
| — | 20 or 120 | ODBC canonical | yyyy-mm-dd hh:mi:ss(24h) |
| — | 21 or 121 | ODBC canonical (with milliseconds) | yyyy-mm-dd hh:mi:ss.mmm(24h) |
| — | 130 | Kuwaiti | dd/mm/yyyy hh:mi:ss.mmmAM |
| — | 131 | Kuwaiti | dd mm yyyy hh:mi:ss.mmmAM |
The following table lists formatting styles that you can use when converting monetary values to character information:
| Value | Output |
|---|---|
| 0 (default) | Two digits behind decimal point |
| 1 | Two digits behind decimal point |
| 2 | Four digits behind decimal point |
In the following example, we format a monetary value:
Select $12345678.90, Convert(varchar(30), $12345678.90, 0), Convert(varchar(30), $12345678.90, 1}, Convert(varchar(30), $12345678.90, 2}
The result is
----------------- ----------------- ---------------- ------------ 12345678.9000 12345678.90 12,345,678.90 12345678.9000
| Value | Output |
|---|---|
| 0 (default) | In scientific notation, when needed; six digits maximum |
| 1 | Eight digits always in scientific notation |
| 2 | Sixteen digits always in scientific notation |
| Tip | Microsoft recommends using the Cast() function whenever the formatting power of Convert() is not required because Cast() is compatible with the ANSI SQL-92 standard. |
When you specify the target data type of variable length as a part of the Cast() or Convert() functions, you should include its length, too. If you do not specify length, SQL Server assigns a default length of 30. Therefore, the previous example could be written as
Select $12345678.90, Convert(varchar, $12345678.90, 0), Convert(varchar, $12345678.90, 1), Convert(varchar, $12345678.90, 2)
You need to use conversion functions when you do any of the following:
-
Supply a Transact-SQL statement or function with a value in a specific data type
-
Set the format of a date or number
-
Obtain a value that uses an exotic data type
In some cases, SQL Server automatically (that is, behind the scenes) converts the value if the required data type and the supplied data type are compatible. For example, if some function requires a char parameter, you could supply a datetime parameter and SQL Server will perform an implicit conversion of the value. In the opposite direction, you must use an explicit conversion—that is, you must use conversion functions. If it is not possible to convert the expression to the specified data type, SQL Server raises an error.
| Tip | SQL Server Books (Mine includes a table that lists which data types can he converted to other data types and which kind of conversion (explicit or implicit) is required. |
Information About the Current Session The following functions return information associated with the current session (for instance, how you logged on to the server, your username in the database, the name of the server, the permissions you have in the current database, and so on):
| Function | Description |
|---|---|
| App_Name() | Name of the application that opened the session. |
| Host_Id() | ID of the computer hosting the client application. |
| Host_Name() | Name of the computer hosting the client application. |
| Permissions() | Bitmap that specifies permissions on a selected column, a database object, or the current database. |
| Current_User | Name of the database user, same as User_Name(). |
| Session_User | Name of the database user who owns the current session. |
| System_User | Name of the server login that owns the current session. If the user has logged on to the server using Microsoft Windows NT Authentication, this function returns the Windows NT login. |
| User_Name() | Name of the database user, same as Current_User. |
The following stored procedure uses the System_User function to identify the user adding an order to the system:
create procedure dbo.ap_Order_Add -- insert Order record @dtmOrderDate datetime = null, @dtmTargetDate datetime = null, @chvUserName varchar(128) = null, @intDestinationLocation int, @chvNote varchar(200), @intOrderid int OUTPUT As declare @intRequestedById int -- If user didn't specify order date -- default is today. if @dtmOrderDate = null Set ©dtmOrderDate = GetDate() -- If user didn't specify target date -- default is 3 days after request date. if @dtmTargetDate = null Set @dtmTargetDate = DateAdd(day, 3, @dtmOrderDate) -- if user didn't identify himself -- try to identify him using login name if @chvUserName = null Set @chvUserName = System_User -- get Id of the user select @intRequestedById = ContactId from dbo.Contact where UserName = @chvUserName -- if you cannot identify user report an error If @intRequestedById = null begin Raiserror('Unable to identify user in Contact table!', 1, 2) return -1 end -- and finally create Order Insert into [Order](OrderDate, RequestedById, TargetDate, DestinationLocationId) Values (@dtmOrderDate, @intRequestedById, @dtmTargetDate, @intDestinationLocation) set @intOrderid = Scope_identity() return | Note | Some functions such as System_User and Current_User do not require and cannot be used with pair of parentheses(). |
Functions for Handling Identity Values Identity columns are used in SQL Server tables to automatically generate unique identifiers for each record. Numbers that are generated in this manner are based on two values—identity seed and identity increment. SQL Server starts assigning identity values from an identity seed, and every row is given a value that is greater than the previous one by the value specified in the identity increment (or less than that value if you use a negative increment value).
In Chapter 3, we covered the use of the @@identity function/global variable. It returns the last value generated by SQL Server while inserting record(s) into the table with an identity value:
Declare @intEqId int Insert into Equipment(Make, Model, EqTypeID) Values ('ACME', 'Turbo', 2} Select @intEqId = @@identity Select @intEqId [EqId] The Scope_Identity() function returns the last identity value generated in the scope of the current process. We will discuss in detail usage of the Scope_Identity() function and the problems it solves in the "Using Identity Values" section of Chapter 15.
Declare @intEqId int Insert into Equipment(Make, Model, EqTypeID) Values ('ACME', 'Turbo', 2} Select @intEqId = Scope_Identity() Select @intEqId [EqId] | Tip | Always use Scope_dentity() instead of @@identity. |
The Ident_Seed() and Ident_Incr() functions return to the user the values of the seed and the increment for the selected table or view:
Select IDENT_SEED('Inventory'}, IDENT_INCR('Inventory'} The Identity() function allows a user to generate identity values while using the Select…Into command. Let me remind you that this command selects records and immediately inserts them into a new table. Without it, you would be forced to create a new table with an identity column and then insert the selected records into the table. With it, everything can be achieved in one step:
SELECT Identity(int, 1,1) AS ID, Property.Property, InventoryProperty.Value, Property.Unit INTO #InventoryProperty FROM dbo.InventoryProperty InventoryProperty INNER JOIN dbo.Property Property ON InventoryProperty.PropertyId = Property.Propertyld WHERE (InventoryProperty.InventoryId = 12)
Ident_Current() returns the last identity value set for a specified table (in any scope of any process). To use it, just supply the table name as a parameter:
Select Ident_Current('Equipment')
Date and Time Functions
The following set of functions is designed to process data and time values and expressions.
Get (Current) Date GetDate() is the function that you will probably use more often than any other date and time function. It will return the system time in datetime format. We have already demonstrated the use of this function in the first section of this chapter, "Using Functions."
GetUtcDate() is the function that returns the date and time for the Greenwich time zone, also known as Universal Time Coordinate (UTC).
Extracting Parts of Date and Time From time to time, you will need to extract just one component of the date and time value. The basic functionality necessary to achieve this end is implemented in the following three functions:
DAY(date) MONTH(date) YEAR(date)
These functions require expressions of the datetime or smalldatetime data type, and they all return the corresponding integer value.
The DatePart() and DateName() functions provide similar functionality, but they are more flexible:
DatePart(datepart, date) DateName(datepart, date)
The user can specify which component of the date to obtain by supplying a datepart constant from Table 4-2 (you can use either the full name or the abbreviation).
| Datepart—Full | Datepart—Abbreviation |
|---|---|
| Millisecond | ms |
| Second | Ss, s |
| Minute | Mi, n |
| Hour | Hh |
| Weekday | Dw |
| Week | wk, ww |
| Dayofyear | Dy, y |
| Day | Dd, d |
| Month | mm, m |
| Quarter | qq, q |
| Year | yy, yyyy |
DatePart() then returns the value of the datepart, and DateName() returns the string that contains the appropriate name. Naturally, DateName() is not meaningful in some cases (for example, year, second) and SQL Server will return the same value as it would for DatePart(). The following Select statement shows how you can use date functions:
SELECT GetDate()'Date', DateName(month, GetDate()) AS 'Month Name', DatePart(yyyy, GetDate()) AS 'Year'
Notice that the first parameter is not a character parameter. You cannot fill it using an expression or variable. SQL Server will return the following:
Date Month Name Year -------------------------- --------------- ------------ 2003-02-20 00:45:40.867 February 2003
Date and Time Calculations Transact-SQL contains two functions for performing calculations on date and time expressions:
DateAdd(datepart, number, date) DateDiff(datepart, startdate, enddate)
DateAdd() is used to add a number of datepart intervals to the specified date value. DateDiff() returns the number of datepart intervals between a startdate and an enddate. Both of these functions use a value from Table 4-2 to specify datepart. The following stored procedure uses these functions to list the due dates for leases:
Alter Procedure ap_Terms_List -- return list of due days for the leasing @dtsStartDate smalldatetime, @dtsEndDate smalldatetime, @chvLeaseFrequency varchar(20) As set nocount on declare @insDueDates smallint -- number of intervals -- calculate number of DueDates select @insDueDates = Case @chvLeaseFrequency When 'monthly' then DateDiff(month, @dtsStartDate, @dtsEndDate) When 'semi-monthly' then 2 * DateDiff(month, @dtsStartDate, @dtsEndDate) When 'bi-weekly' then DateDiff(week, @dtsStartDate, @dtsEndDate)/2 When 'weekly' then DateDiff(week, @dtsStartDate, @dtsEndDate) When 'quarterly' then DateDiff(qq, @dtsStartDate, @dtsEndDate) When 'yearly' then DateDiff(y, @dtsStartDate, @dtsEndDate) END -- generate list of due dates using temporary table Create table #DueDates (ID int) while @insDueDates >= 0 begin insert #DueDates (ID) values (@insDueDates) select @insDueDates = @insDueDates - 1 end -- display list of Due dates select ID+1, Convert(varchar, Case When @chvLeaseFrequency = 'monthly' then DateAdd(month, ID, @dtsStartDate) When @chvLeaseFrequency = 'semi-monthly' and ID/2 = CAST(ID as float)/2 then DateAdd(month, ID/2, @dtsStartDate) When @chvLeaseFrequency = 'semi-monthly' and ID/2 <> CAST(ID as float)/2 then DateAdd(dd, 15, DateAdd(month, ID/2, @dtsStartDate)) When @chvLeaseFrequency = 'bi-weekly' then DateAdd(week, ID*2, @dtsStartDate) When @chvLeaseFrequency = 'weekly' then DateAdd(week, ID, @dtsStartDate) When @chvLeaseFrequency = 'quarterly' then DateAdd(qq, ID, @dtsStartDate) When @chvLeaseFrequency = 'yearly' then DateAdd(y, ID, @dtsStartDate) END , 105) [Due date] from #DueDates order by ID -- wash the dishes drop table #DueDates return
You can see the result of the stored procedure in Figure 4-2.
Figure 4-2: Execution of ap_Terms_List
String Functions
Microsoft SQL Server supports a relatively elaborate set of string functions. (Who would expect such a thing from a tool originally developed in C?)
Basic String Manipulation The Len() function uses the following syntax:
Len(string_expression)
This function returns the length of a string in characters. The input parameter can be any kind of string expression. DataLength(), a similar system function, returns the number of bytes occupied by the value.
declare @chvEquipment varchar(30) set @chvEquipment = 'Toshiba Portege 7020CT' select Len(@chvEquipment)
The result is
----------- 22
The following two functions return the number of characters from the left or right side of the string:
Left(character_expression, integer_expression) Right(character_expression, integer_expression)
Early versions of Microsoft SQL Server contained only the Right() function:
declare @chvEquipment varchar(BO) set @chvEquipment = 'Toshiba Portege 7020CT' select Left(@chvEquipment, 7) Make, Right(@chvEquipment, 14) Model
The result of this batch is
Make Model --------- -------------- Toshiba Portege 7020CT
Before the introduction of the Left() function, developers had to implement its functionality using the SubStringO function:
Substring(expression, start, length)
The SubString() function returns a set (length) of characters from the string (expression) starting from a specified (start) character. The expression can be any character or any text, image, or binary data type. Because of this data type flexibility, the length and start parameters are based on the number of bytes when the expression is of the text, image, binary, or varbinary data types, rather than on the number of characters. In the case of Unicode data types, one character occupies 2 bytes. If you specify an odd number, you may get unexpected results in the form of split characters.
The following batch extracts part of a string:
declare @chvEquipment varchar(30) set @chvEquipment = 'Toshiba Portege 7020CT' select Substring(@chvEquipment, 9, 7)
The result set is
------- Portege
The Charlndex() function returns the index of the first occurrence of a string (expression1) within a second string (expression2):
Charlndex(expressionl, expression2 [, start_location])
There is an optional parameter that allows you to specify the start location for the search:
Create Procedure ap_SplitFullName -- split full name received in format 'Sunderic, Dejan' -- into last and first name -- default delimiter is comma and space ', ', -- but caller can specify other @chvFullName varchar(50), @chvDelimiter varchar(B) = ', ', @chvFirstName varchar(50) OUTPUT, @chvLastName varchar(50) OUTPUT As set nocount on declare @intPosition int Set @intPosition = Charlndex(@chvDelimiter, @chvFullName) If @intPosition > 0 begin Set @chvLastName = Left(@chvFullName, @intPosition - 1) Set @chvFirstName = Right(@chvFullName, Len(@chvFullName) - @intPosition - Len(@chvDelimiter) ) end else return 1 return 0
All of these string functions might look to you like a perfect tool for searching table columns, but there is just one problem with this application. If you apply a conversion function inside the Where clause of a Select statement, SQL Server does not use the index to query the table. Instead, it performs a table scan—even if the index exists. For example, you should not use the Charlndex() function to identify records with a particular string pattern:
select * from Equipment where Charlndex('Portege', Model) > 0 The Like operator with wild card characters is a much better choice if the string that you are looking for is at the beginning of the field:
select * from Equipment where Model like 'Portege%'
The PatIndex() function is similar to the Charlndex() function:
PatIndex('%pattern%', expression) The major difference is that it allows the use of wild card characters in the search pattern:
Set @intPosition = PATINDEX('%,%', @chvFullName) Again, if you use this function to search against a table column, SQL Server ignores the index and performs a table scan.
| Tip | In early versions of SQL Server, PatIndex() was the only reasonable (although not very fast) way to query the contents of text columns and variables. Since version 7.0, SQL Server has had a new feature—FullText Search—that allows linguistic searches against all character data and works with words and phrases instead of with character patterns. Basically, Microsoft has included parts of a Windows Server tool called Index Server in the Workgroup, Standard, and Enterprise editions of SQL Server 2005,2000, and 7.0. |
String Conversion The following two functions remove leading and trailing blanks from a string:
LTrim(character_expression) RTrim(character_expression)
In the following query, we use both of them at the same time:
select LTrim(RTrim(' Dejan Sunderic '}} The following functions convert a string to its uppercase or lowercase equivalent:
Upper(character_expression) Lower(character_expression)
Use the Str() function to convert numeric values to strings:
Str(float_expression [, length [, decimal]])
The length parameter is an integer that specifies the number of characters needed for the result. This parameter includes everything: sign, digit, and decimal point. If necessary to fit the output into the specified length, SQL Server will round the value before converting it. If you do not specify a length, the default length is ten characters and the default decimal length is 0.
SQL Server provides a number of functions for representing the conversion from character types to ASCII codes and vice versa:
Char(integer_expression) ASCII(character_expression) NChar(integer_expression) Unicode(character_expression)
The Char() and NChar() functions return characters with the specified integer code according to the ASCII and Unicode standards:
select NChar(352) + 'underi' + NChar(263)
Depending on fonts, operating systems, language settings, and other criteria, you may get proper or improper results from this expression (see Figure 4-3).
Figure 4-3: Using Unicode characters
There is another interesting use of the Char() function. You can use it to insert control characters into output. For example, you can add tabulators Char(9) or carriage returns Char(13). In the past, this was a very important way to format output.
The ASCII() and Unicode() functions perform the opposite operation. They return the integer that corresponds to the first character of an expression (see Figure 4-4).
Figure 4-4: Identifying Unicode character
The following two functions generate a string of a specified length (integer_ expression) and fill it with spaces or a specified character:
Space(integer_expression) Replicate(character_expression, integer_expression)
For example:
select Space(4) + Replicate('*', 8} This statement returns a useless result, but these functions were used at one time primarily to format output:
------------ ********
Use the Stuff() function to stuff a string:
Stuff(character_expression1, start, length, character_expression2)
SQL Server removes a length of character_expression1, beginning at the specified start point, and replaces it with character_expression2. The specified length does not have to match that of character_expression2:
select Stuff('Sunderic, Dejan', 9, 2, Char(9))
This query replaces the comma and space in the target string with a tabulator:
------------------ Sunderic Dejan
Metadata Functions
These functions are like a drill that you can use to obtain information about a database and database objects. The following table contains a partial list of metadata functions:
| Function | Description |
|---|---|
| Col_Length(table, column) | Returns the length of the column. |
| Col_Name(table_id, column_id) | Returns the name of the column specified by table identification number and column identification number. |
| ColumnProperty(id, column, property) | Returns information about a column or stored procedure parameter. |
| DatabaseProperty(database, property) | Returns the value of the named database property for a given database and property name. |
| DatabasePropertyEx(database, property) | Returns the value of the named database property for a given database and property name. The returned value is of the sql_variant data type. |
| Db_Id(database) | Returns the database identification number for the given database. |
| Db_Name(database_id) | Returns the database name for a given database identification number. |
| lndex_Col(table, index_id, key_id) | Returns the indexed column name. |
| lndexProperty(table_id, index, property) | Returns the value of the given property for a given table identification number and index name. |
| Object_Id(object) | Returns the identification number of the given object. |
| Object_Name(oject_id) | Returns the database object name for the given object identification number. |
| ObjectProperty(id, property) | Returns information about the specified property for a given object's identification number. |
| @@ProcID | Returns the identification number of the current stored procedure. |
| Sql_Variant_Property(expression, property) | Returns the value of the given property for a given expression. |
| TypeProperty(type, property) | Returns information about the data type. |
The Sql_Variant_Property() function retrieves information about the sql_variant data type. It returns specified property information about data stored in or obtained from the expression parameter. You can specify one of the following properties to be returned:
| Property | Output |
|---|---|
| BaseType | The SQL Server data type |
| Precision | Number of digits of the base type |
| Scale | Number of digits behind decimal point |
| TotalBytes | Number of bytes required to store data and metadata |
| Collation | Collation of the data |
| MaxLength | Maximum length in bytes |
The Sql_Variant_Property() function uses the following syntax:
SQL_Variant_Property(expression, property)
The property parameter must be specified in the form of a string:
SELECT SQL_Variant_Property(Lookup,'BaseType'), SQL_Variant_Property(Lookup,'Precision'), SQL_Variant_Property(Lookup, 'Scale') FROM Lookup WHERE LookupGroupId = 16 AND LookupId = 4
Aggregate Functions
Aggregate functions perform an operation on a set of records and return a single value. They can be used in the following situations:
-
The selection list of the Select statement
-
A Having clause
-
A Compute clause
The following table contains a list of aggregate functions:
| Function | Description |
|---|---|
| Avg([All | Distinct] expression) | Returns the average value in the group. |
| Count([All | Distinct] expression |*) | Counts the number of items in the group. |
| Count_Big([All | Distinct] expression |*) | Counts the number of items in the group. The result is returned in the form of a bigint number. |
| Grouping(Column_Name) | Creates an additional column with a value of 1 when a row is added by the CUBE or ROLLUP operator, or 0 if it is not the result of a CUBE or ROLLUP operator. |
| Max(expression) | Returns the maximum value in the expression. |
| Min(expression) | Returns the minimum value in the expression. |
| Sum(expression) | Returns the sum of the expression's values. |
| StDev(expression) | Returns the statistical standard deviation for the values in the expression. |
| StDevP(expression) | Returns the statistical standard deviation for the population for the values in the expression. |
| Var(expression) | Returns the statistical variance of the values in the expression. |
| VarP(expression) | Returns the statistical variance for the population for the values in the expression. |
Except for the Count() function, all aggregate functions ignore records that have null in the specified field from the set:
select Avg(Rent) [Average Rent] from Inventory
As you can see, SQL Server will even print a warning about nulls:
Average Rent ------------ 200.0000 (1 row(s) affected) Warning: Null value eliminated from aggregate.
You apply Count() on a specific field:
select Count(Rent) [Rentals] from Inventory
SQL Server will count only records that do not have null in the Rent field:
Rentals ---------- 241 (1 row(s) affected) Warning: Null value eliminated from aggregate.
You can apply Count() on all fields:
select Count(*) [Assets] from Inventory
SQL Server counts all records in the table:
Assets ------------- 7298 (1 row(s) affected)
Ranking Functions
SQL Server 2005 introduced a set of functions for determining a rank of records in the result set. The simplest of them is the function Row_Number(). It returns the record's position in the result set and it is easiest to explain it in an example. The following query returns the equipment count from the Inventory table per location:
select LocationId, count(*) InvCount from Inventory group by LocationId order by InvCount
Its result would be something like this:
LocationId InvCount ----------- ----------- 2 10 17 39 9 43 7 43 21 44 8 44 3 46 13 46 5 47 11 47 16 48 6 48 ...
I will make this query a derived table called InvCount in the From clause of a new query. I will repeat columns of the derived table in the Select clause of the outer query. Then I will add a new column with the Row_Number() function. The function call contains an Over clause that must contain an Order By clause. The Order By clause specifies the field that the row numbering (and ordering) will be based on:
select LocationId, InvCount, row_number() over (order by InvCount) row_number from (select LocationId, count(*) InvCount from Inventory group by LocationId) InvCount
The result is
LocationId InvCount row_number -------------- ------------ --------------------- 2 10 1 17 39 2 9 43 3 7 43 4 21 44 5 8 44 6 3 46 7 13 46 8 5 47 9 11 47 10 ...
Row numbering is perfect for some uses, but sometimes it must be recognized that that some locations (records) have the same inventory count (rank in the result set). The question is just how to declare those numbers. For example, we can easily agree that locations 9 and 7 should share third place, but what about locations 21 and 8? Should they share fourth or fifth place? Some projects will rank them one way and some another way. Fortunately, we have functions for both:
select LocationId, InvCount, row_number() over (order by InvCount) row_number, Rank() over (order by InvCount) Rank, Dense_Rank() over (order by InvCount)Dense_Rank from (select LocationId, count(*) InvCount from Inventory group by LocationId) InvCount
result of the query is
LocationId InvCount row_number Rank Dense_Rank ---------- ------------ -------------- -------- ----------- 2 10 1 1 1 17 39 2 2 2 9 43 3 3 3 7 43 4 3 3 21 44 5 5 4 8 44 6 5 4 3 46 7 7 5 13 46 8 7 5 5 47 9 9 6 11 47 10 9 6 ...
Rowset Functions
Functions of this type are distinguished from other functions in that they return a complete recordset to the caller. They cannot be used (as is the case of scalar functions) in any place where an expression is acceptable. They can be used in Transact-SQL statements only in situations where the server expects a table reference. An example of such a situation is the From clause of the Select statement.
Pass-through Functions
The OpenQuery() function is designed to return a recordset from a linked server. It can be used as a part of Select, Update, Insert, and Delete Transact-SQL statements. The Query parameter must contain a valid SQL query in the dialect of the linked server, since the query will be executed (as-is—as a pass-through query) on the linked server. This function uses the following syntax:
OpenQuery(1inked_server, 'query')
| Note | Linked servers are OLE DB data sources that are registered on the local SQL server. After registration, the local server knows how to access data on the remote server. All that is needed in your code is a reference to the name of the linked server. |
You can register a linked server to be associated with the Northwind.mdb sample database either from Management Studio or using the following code:
EXEC sp_addlinkedserver @server = 'employees_Access', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:\20051009\mydoc\employees.mdb' Go
Then, you can use the OpenQuery() function to return records from the linked server:
SELECT * FROM OpenQuery(employees_Access, 'SELECT * FROM employees')
It is much simpler to create a linked server connection toward SQL Server and use it:
EXEC sp_addlinkedserver 'SS2K_Asset2000', N'SQL Server' GO select * from SS2K_Asset2000.Asset2000.dbo.Inventory
OpenRowset() is very similar to the OpenQuery() function:
OpenRowset( 'provider_name', {'datasource';'user_id';'password' | 'provider_string' }, { [catalog.][schema.]object | 'query'} ) It is designed for connecting to a server that is not registered as a linked server. Therefore, you must supply both the connection parameters and the query in order to use it. There are several options for defining the connection, such as OLE DB, ODBC, and OLE DB for ODBC, along with two options for specifying a result set: a pass-through query or a valid name for a database object.
The following query joins one table from the remote SQL Server with two tables on the local SQL Server:
SELECT a.au_lname, a.au_fname, titles.title FROM OPENROWSET('SQLNCLI', 'Server=LG\ss2k;Trusted_Connection=yes;', 'SELECT * FROM pubs.dbo.authors') AS a INNER JOIN titleauthor ON a.au_id = titleauthor.au_id INNER JOIN titles ON titleauthor.title id = titles.title_id | Tip | Although OpenRowset() will work fine, if you plan repetitive use of some data source, you should consider registering a linked server and using OpenQuery(). The execution of OpenQuery() will he considerably faster. |
Microsoft has delivered several new OLE DB data providers that can be used with the OpenRowset() function. The "Binary Data Types" section of Chapter 3 contains an interesting example of the usage of the new BULK provide that returns content of the file as a rowset. You can also use it to load fields from a text file into SQL Server.
OpenXML Function
OpenXML() provides access to an in-memory rowset that T-SQL developers can use to parse, access, and return the content of an XML document.
Before an XML document can be accessed using OpenXML(), the document must be loaded into memory using sp_xml_preparedocument. The stored procedure reads the XML document provided in xmltext, parses the document using the MSXML parser, and places the document into an in-memory structure. It is a tree that contains assorted nodes such as elements, attributes, comments, and text. The stored procedure returns a handle for the XML document hdoc that OpenXML() can use to access the information, and that sp_xml_removedocument uses to remove the document from memory. In the following example, an XML document is loaded in memory and its handle is recorded in the @intDoc variable:
DECLARE @intDoc int DECLARE @chvXMLDoc varchar(max) -- sample XML document SET @chvXMLDoc =' <root> <Equipment Equipment Make="Toshiba" Model="Portege 7020CT"> <Inventory Inventory Status Equipment/> <Inventory Inventory Status Equipment/> </Equipment> </root>' --Load the XML document into memory. EXEC sp_xml_preparedocument (SintDoc OUTPUT, @chvXMLDoc
OpenXML() has the following syntax:
OpenXML(hdoc, rowpattern, flags) [With (SchemaDeclaration | TableVariable)]
hdoc is a handle that points to the tree containing the XML data, rowpattern is the XPath string used to identify nodes that need to be processed, flags is an optional parameter that controls the way that data from the XML document is mapped to the rowset and how data is to be copied to the overflow property (I will explain this a little further on in this section).
SchemaDeclaration is a declaration of the structure in which data will be returned. Alternatively, it is possible to use the name of a table variable (TableVariable) instead. The rowset will be formed using the structure of the table variable. The SchemaDeclaration can be composed using the following syntax:
ColName ColType [ColPattern | MetaProperty] [, ColName ColType [ColPattern MetaProperty]...]
ColName is the name and ColType is the data type of the column. This structure is very similar to the table structure of the Create Table statement. ColPattern is an optional parameter that defines how a column is to be mapped to the XML node. A MetaProperty is specified to extract metadata such as data types, node types, and namespace information.
Finally, take a look at an example that uses all these constructs:
DECLARE @intDoc int DECLARE @chvXMLDoc varchar(max) -- sample XML document SET @chvXMLDoc = '<root> <Equipment Equipment Make="Toshiba" Model="Portege 7020CT"> <Inventory Inventory Status Equipment/> <Inventory Inventory Status Equipment/> </Equipment> <Equipment Equipment Make="Sony" Model="Trinitron 17XE"/> <Equipment Equipment Make="HP" Model="LaserJet 4"/> <Equipment Equipment Make="Bang & Olafson" Model="V4000"> <Inventory Inventory Status Equipment/> </Equipment> <Equipment Equipment Make="NEC" Model="V90"> <Inventory Inventory Status Equipment/> </Equipment> </root>' --Load the XML document into memory. EXEC sp_xml_preparedocument @intDoc OUTPUT, @chvXMLDoc -- SELECT statement using OPENXML rowset provider SELECT * FROM OPENXML (@intDoc, '/root/Equipment/Inventory', 8) WITH (InventoryId int ' @Inventory-ID' , StatusID int '@StatusID', Make varchar(25) '../@Make', Model varchar(25) '../@Model', Comment ntext '@mp:xmltext') -- remove the XML document from memory EXEC sp_xml_removedocument @intDoc
The result is shown in Figure 4-5.
Figure 4-5: Using OpenXML()
As soon as it is no longer used, the document should be removed from memory using sp_xml_removedocument.
| Note | The memory is not released until sp_xml_removedocument is called, and hence it should he called as soon as possible. |
In the preceding example, the OpenXML() rowset provider is used in a Select statement:
SELECT * From Openxml (@intDoc, '/root/Equipment/Inventory', 8) WITH (InventoryId int '@InventoryId', StatusID int '@StatusID', Make varchar(25) '../@Make', Model varchar(25) '../@Model', Comment ntext '@mp:xmltext')
The rowpattern parameter specifies that information will be extracted (mostly) from Inventory nodes ('/root/Equipment/Inventory ').
The InventoryId and StatusID columns in the rowset are filled from the attribute data. In the XPath language (unfortunately beyond the scope of this book), the @ character is used as an abbreviation that points to attribute nodes.
The Make and Model columns are not in the same group of nodes as InventoryId and StatusID. Since they are attributes of the Equipment node, ColPattern has to refer to the parent node ('…/@Model') first.
The third parameter of the OpenXML() clause sets the way that the function interprets the XML and handles the overflow (data that does not get transferred into other record columns). You have to set it to 1 or 0 (default value) if the XML document is attribute-centric or to 2 if the document is element-centric. The value 8 in the preceding example specifies that the last column (Comment) should be filled with content of XML nodes that are not included in other columns.
Table 4-3 shows a list of possible values of the flags parameter.
| Mnemonic | Value | Description |
|---|---|---|
| XML_ATTRIBUTES | 1 | Attribute-centric mapping. |
| XML_ELEMENTS | 2 | Element-centric mapping. |
| XML_DEFAULT | 0 | Default-equivalent to XMLATTRIBUTES (1). |
| XML_NOCOPY | 8 | Overflow metaproperty of the document (@mp:xmltext) should contain only nodes that were not extracted using the OpenXML() rowset provider. |
XML_NOCOPY could be combined (logical OR) with XML_ATTRIBUTES (1 + 8 = 9) or XML_ELEMENTS (2 + 8 = 10). This flag can be used to generate either a string with the overflow information or a string with a complete branch of the XML document. The following example extracts the branch of the XML document/tree that describes a node with EquipmentID set to 1:
Select * From Openxml (@intDoc, '/root/Equipment', 2) With (EquipmentID int '@EquipmentID', Branch ntext '@mp:xmltext') Where EquipmentId = 1
SQL Server returns the following:
EquipmentID Branch ----------- --------------------------------------------------- 1 <Equipment Equipment Make="Toshiba" Model="Portege 7020CT"> <Inventory Inventory Status Equipment/> <Inventory Inventory Status Equipment/> </Equipment> (1 row(s) affected)
EAN: 2147483647
Pages: 165