There are many cases where you need to have an operation performed on the information in your table prior to returning it to a SELECT request or for use in another project. When this happens, you can either write some logic yourself and use it to work with the information coming back, or you can look to SQL Server to provide some of this functionality for you.
An example of letting SQL Server do the work for you is the COUNT() function, probably one of the most used functions provided with SQL Server. COUNT does just what its name indicates. It counts the rows that you indicate in your query. You find out more about this specific function later, but it's important to understand some key elements to functions. First, they execute on the server, saving on bandwidth. Second, they run faster than if they were executing on your local system because they have immediate access to your database information.
Functions execute a section of code that performs an operation, which returns a desired value. For the function to perform its operation, you must usually supply the required data as a list in which each element is called a parameter. You can use functions with columns of data and other storage structures of Transact-SQL. You can also use functions in the SELECT or Where clause of SELECT statements, in expressions, and, for SELECTed functions, such as system and niladic functions, in constraint-defined tables or views.
NOTE: Niladic functions are special functions for use with constraints you set up for your tables. Niladic functions return a user or timestamp value and are useful for inserting a default value in the table.
A small subset of Transact-SQL functions illustrate how functions are used in Transact-SQL statements. Also, the subset of SQL functions are generic and are typically available in any dialect of SQL.
NOTE: If you've worked with another dialect of SQL, then you're probably familiar with the hand- ful of basic functions. Unfortunately, the set of functions, used across different vendors' dialects of SQL, is extremely small. The remaining functions may be comparable across server database SQL dialects, though they aren't identical.
Some of the basic Transact-SQL functions are shown in Table 9.1.
Function | Operation |
AVG | Average |
SUM | Sum |
MIN | Minimum value |
MAX | Maximum value |
COUNT | Count |
These, and other functions in a SELECT clause, are used as if they are column identifiers. They return their results as columns in the resulting data set.
The objects or arguments of a function must be enclosed in parentheses. If the function requires more than a single argument, the arguments are separated by a comma (,).
The syntax for the use of functions in the SELECT clause of a SELECT statement is as follows:
SELECT function (column_1 or *),...function (column_n) FROM table
Note that NULL values aren't used for computation of AVERAGE, SUM, MIN, MAX. If all elements of a set are NULL, the function return is NULL. COUNT, when used with an asterisk (*), determines the number of rows in a column, even if it contains NULL values.
The AVG function returns the arithmetic average of the column values referenced. In the fol- lowing example, AVG is used to return the average of the Pay Rate column for all rows of the Pays table.
SELECT avg(pay_rate) from pays
The COUNT function returns the numbers of columns that match the SELECTion expression. The asterisk wild card (*) is used as an argument for the COUNT function. If * is used in place of the column name in a SELECT clause, the asterisk specifies all rows that meet the criteria of the SELECT statement. The COUNT function counts all table rows that meet the criteria. The following syntax is used with the COUNT function:
SELECT COUNT(column_name) FROM table_name
For example, the following SELECT statement returns the number of rows in the Employees table:
SELECT count(*) from employees
If a WHERE clause is used in your SELECT statement, the COUNT function applies to only the rows that match the criteria specified in the WHERE clause. For example, the following COUNT statement returns the number of employees in the Sales Department:
SELECT count(*) from employees where department='Sales'
TIP: You can improve the performance of the COUNT function by specifying a column name to count and making sure that the column you specify is both indexed and not NULL. By doing so, SQL Server can use its optimization techniques to return the count of the rows more quickly.
MAX returns the largest value in a column. The syntax of MAX is as follows:
SELECT MAX(column_name) FROM table_name
In the following example, MAX is used to return the maximum, or greatest number of hours_worked, for all rows of the Pays table.
SELECT max(hours_worked) from pays
MIN returns the smallest value in a column. In the following example, MIN is used to return the minimum number of hours_worked for all rows of the Pays table.
SELECT min(hours_worked) from pays
In the next example, MIN is used to return the lowest rate of pay for employees in the Field Service department. Both the Pay and Employees tables must be referenced in the SELECT statement because the Department column is in the Employees table, while the Rate column is in the Pays table. The corresponding badge numbers in each table are used to combine the appropriate rows.
SELECT min(rate) from employees,pays where employees.badge=pays.badge and department='Field Service'
SUM returns the summation of such entities as column values. The SUM function returns the total of the non-NULL values in the numeric expression, which is often just a column name, that follows the SUM keyword. The syntax for SUM is as follows:
SUM([DISTINCT] <expression>)
In the following example, the result would be the sum of the hours_worked for all rows of the Pays table displayed:
SELECT sum (hours_worked) from pays
TIP: Rows that contain a NULL value in the column referenced by the SUM function are automatically skipped in the calculation.
You can use multiple functions within a single statement. The following example returns the average, minimum, and maximum of the hours_worked column in the Pays table:
SELECT avg(hours_worked), min(hours_worked), max(hours_worked) from pays
In the following, more complicated example, a SELECT statement is used to return the maximum and average rate, minimum hours_worked, and the count of all rows of the Employees table:
SELECT max(rate),min(hours_worked),avg(rate),count(*) from employees,pays where employees.badge=pays.badge
If the COUNT function is used to reference a column name, it returns the number of values. The COUNT function includes duplicates in its count, but it doesn't include NULL values. If you add the keyword DISTINCT, the COUNT function returns the number of each unique value. The following syntax for the COUNT function is used with the keyword DISTINCT in a SELECT statement:
SELECT COUNT(DISTINCT column_name) FROM table_name
In the following example, the keyword DISTINCT is used with the COUNT function in a SELECT statement to display the number of different departments in the Employees table:
SELECT count(distinct department) from employees
The CUBE and ROLLUP operators were added to SQL Server 6.5 to make it easier to access large amounts of data in a summary fashion. When a SELECT statement is cubed, aggregate functions are transformed into super-aggregate functions that return only the rows necessary to report a summary of the information requested. The rollup operator differs from cube only because it is sensitive to the order of columns in the GROUP BY clause.
NOTE: There are several things to be aware of when using the CUBE and ROLLUP operators. First, a GROUP BY column list can be no more than 900 bytes. Second, there is a maximum of 10 columns. Next, columns or expressions must be specified in the GROUP BY clause. GROUP BY ALL can't be used. Finally, these operators are disabled when trace flag 204 is on.
Book sales are a perfect example. A query that returns a book title and the number of books ordered for each invoice in a database would return a row for each invoice. If the cube operator were applied to this query, it would only return a row for each title and the total quantity ordered for that title.
Functions are used to perform various operations on binary data, character strings, or expressions, including string concatenation. String functions are used to return values commonly needed for operations on character data. The following list shows the set of string functions:
ASCII | PATINDEX | SPACE |
CHAR | REPLICATE | STR |
CHARINDEX | REVERSE | STUFF |
DIFFERENCE | RIGHT | SUBSTRING |
LOWER | RTRIM | UPPER |
LTRIM | RTRIM | + |
String functions are usually used on CHAR, VARCHAR, BINARY, and VARBINARY datatypes, as well as datatypes that implicitly convert to CHAR or VARCHAR. For example, you can use the PATINDEX function on CHAR, VARCHAR, and TEXT datatypes.
You can nest string functions so that the results returned by an inner function are available for the operation performed by the outer function. If you use constants with string functions, you should enclose them in quotation marks. String functions are usually used in SELECT or WHERE clauses.
CAUTION: You should ensure that the result returned by a nested function is compatible as input to the function in which it's embedded. In other words, if your function is expecting a string variable, be sure that the nested function returns a string, not a numeric value. Check your functions and datatypes carefully to determine if they're compatible. Otherwise, the set of functions can't work correctly.
ASCII returns the ASCII code value of the leftmost character of a character expression. The syntax of the ASCII function is as follows:
ASCII(<char_expr>)
NOTE: Remember that ASCII only returns the code associated with the leftmost character. If you need to have the ASCII value associated with the remaining portion of the string, you need to write a function that can walk down the string and return each value in succession.
CHAR converts an ASCII code into a character. If you don't enter the ASCII code within the range of values between zero and 255, a NULL is returned. The syntax of the CHAR function is as follows:
CHAR(<integer_expr>)
In the following example, the ASCII and CHAR functions are used to convert a character to the decimal ASCII value and the decimal ASCII value to a character:
SELECT ascii(`Able'),char(65) ----------- - 65 A
SOUNDEX returns a four-digit, or SOUNDEX, code, which is used when comparing two strings with the DIFFERENCE function. SOUNDEX could be used to search for duplicates with similar spellings in a mailing list. SOUNDEX can also be used in a word processor to return words that are similar to one that is misspelled.
The syntax for use of the SOUNDEX function is as follows:
SOUNDEX(<char_expr>)
SOUNDEX ignores all vowels unless they're the first letter of a string. In the following example, SOUNDEX is used to return evaluation values for a series of strings.
SELECT soundex (`a'),soundex (`aaa'),soundex (`b'),soundex (`red'), soundex (`read') ----- ----- ----- ----- ----- A000 A000 B000 R300 R300 SELECT soundex (`right'),soundex (`write') ----- ----- R230 W630
DIFFERENCE returns the difference between the values of two character expressions returned by SOUNDEX. The difference is rated as a value from zero to four, with a value of four as the best match. Define the threshold within the range zero to four and perform subsequent operations defined by your criteria. The syntax of the DIFFERENCE function is as follows:
DIFFERENCE(<char_expr1>, <char_expr2>)
In the following example, the difference between the and teh is four, a value that is considered a good match. If you were using DIFFERENCE along with SOUNDEX in a program such as a spelling checker, teh can be treated as a misspelling of "the."
SELECT difference(soundex(`the'),soundex(`teh')) ----------- 4
NOTE: The value that is returned by the DIFFERENCE function is fixed according to the design of the DIFFERENCE function. You must decide how you use the value returned. In the example, a value of four means that the two character strings, the and teh, are as alike as they can be using the soundex scale of values.
If you're looking for a misspelling of a department stored in the Department column of a table such as Employees, a value of three or less may be a different department or a misspelling of a department.
LOWER, which converts uppercase strings to lowercase strings, uses the following syntax:
LOWER(<char_expr>)
UPPER, which converts lowercase strings to uppercase strings, uses the following syntax:
UPPER(<char_expr>)
In the following example, UPPER and LOWER are used to convert a mixed-case string to all- uppercase and all-lowercase:
SELECT upper(`Bob Smith1234*&^'),lower(`Bob Smith1234*&^') ---------------- ---------------- BOB SMITH1234*&^ bob smith1234*&^
LTRIM removes leading spaces from a string. To save space, you can remove leading spaces from a string before it's stored in the column of a table. The leading spaces can also be removed before you perform additional processing on the string. LTRIM uses the following syntax:
LTRIM(<char_expr>)
In the following example, LTRIM is used to remove leading spaces from a string:
SELECT ltrim(` middle `) -------------- middle
NOTE: In this example, the returned value of (`____middle____') still contains trailing spaces. You need to use the next function, RTRIM, to remove trailing spaces.
RTRIM removes trailing spaces from a string. As with LTRIM, trailing spaces can be re- moved before you store the string in the column of a table. Like LTRIM, RTRIM can be used to remove trailing spaces before you perform further processing on the string. RTRIM uses the following syntax:
RTRIM(<char_expr>)
NOTE: In many cases, you want to work with the string without any leading or trailing spaces. Remember that you can nest these functions, so you can use the syntax as indicated in the following example:SELECT RTRIM(LTRIM(` middle `)This example returns only the word "middle", with no spaces surrounding it.
CHARINDEX returns the starting position of the specified character expression within a specified string. The first parameter is the character expression, and the second parameter is an expression, usually a column name, in which SQL Server searches for the character expression. CHARINDEX cannot be used with Text and Image datatypes. The syntax of the CHARINDEX function is as follows:
CHARINDEX(<`char_expr'>, <expression>)
In the following example, CHARINDEX returns the starting character position of the word "Service" in a row of the Department column of the table Employees. An uppercase S, the first letter in Service, is the seventh character in the Field Service department.
SELECT charindex(`Service',department) from employees where name='Stan Humphries' ----------- 7
NOTE: CHARINDEX can be used with other functions. The value returned by CHARINDEX can be used with other functions to extract parts of strings from within other strings. For example, CHARINDEX could be used within the string expression in the second argument of SUBSTRING.
PATINDEX returns the starting position of the first occurrence of substring in a string such as the value of a table column. If the substring isn't found, a zero is returned. You can use a PATINDEX function with data stored as CHAR, VARCHAR, and TEXT datatypes.
Wild card characters can be used in the substring as long as the percent sign (%) precedes and follows the substring. The syntax PATINDEX is as follows:
PATINDEX(`%substring%', <column_name>)
In the following example, PATINDEX returns the character position for the first character of the substring within the string of characters stored in the department for the employee Stan Humphries. Stan Humphries is a member of the Field Service department.
SELECT patindex(`%erv%',department) from employees where name='Stan Humphries' ----------- 8
REPLICATE returns multiple sets of characters specified in the first argument of the function. The second argument specifies the number of sets to be returned. If the second argument, an integer expression, is a negative number, the function returns a NULL string. The syntax of REPLICATE is as follows:
REPLICATE(character_expression, integer_expression)
In the following example, REPLICATE returns a string of identical characters and also returns two iterations of the same sequence of two characters:
SELECT replicate (`a',5),replicate(`12',2) ----- ---- aaaaa 1212
REVERSE returns the reverse order of a string of characters. The character string argument can be a constant, a variable, or a value of a column. The syntax REVERSE is as follows:
REVERSE(character_string)
In the following example, the example would return the two constant strings that are enclosed in quotation marks, but their contents would be reversed:
SELECT reverse(`12345678910'),reverse(`John Smith') ----------- ---------- 01987654321 htimS nhoJ
In the following example, the result is a table column displayed without REVERSE. The same column is displayed in a different order using the REVERSE attribute. Finally, the same string that is the name of the column of the Employees table is processed as a constant because it's enclosed in parentheses:
SELECT name,reverse(name),reverse(`name') from employees where name='Bob Smith' name -------------------- -------------------- ---- Bob Smith htimS boB eman
RIGHT returns part of a character string, starting at the number of characters from the right, as specified in the function argument. If the number of characters in the integer expression argument is negative, perhaps as the result of a nested function, RIGHT returns a NULL string. The syntax of the RIGHT function is as follows:
RIGHT (character_expression, integer_expression)
The following example shows two identical strings, one that is displayed with a RIGHT function and also without a function. The second parameter of the RIGHT function 4 specifies to return from four characters from the end of the string to the rightmost character of the string.
SELECT `12345678', right (`12345678',4) -------- ---- 12345678 5678 (1 row(s) affected)
CAUTION: You can't use a function, such as the RIGHT function, on TEXT or IMAGE datatypes. You must use the specialized set of string handling functions with TEXT and IMAGE datatypes. These special functions are discussed later in this chapter in the section titled "Using TEXT and IMAGE Functions."
SPACE returns a string of spaces for the length specified by the argument to the function. If the argument integer value is negative, SPACE returns a NULL string. The SPACE syntax is as follows:
SPACE(<integer_expr>)
In the following example, SPACE returns multiple spaces between two string constants:
SELECT `begin',space(15),'end' ----- --------------- --- begin end
STR converts numeric data to character data. The STR syntax is as follows:
STR(<float_expr>[, <length>[, <decimal>]])
You should ensure that both the length and decimal arguments are non-negative values. If you don't specify a length, the default length is 10. The value returned is rounded to an integer by default. The specified length should be at least equal to or greater than the part of the number before the decimal point plus the number's sign. If <float_expr> exceeds the specified length, the string returns ** for the specified length.
In the following example, a series of constant numbers is converted to strings. The first number is completely converted because the second argument, the length, specifies the correct size of the resultant string, five numeric digits, the minus sign (-), and the decimal place (.). When the same constant value is converted using a length of six, the least-significant digit is truncated.
The third constant is correctly displayed using a length of six because it's a positive number. The same constant can't be displayed with a length of two, so two asterisks (**) are displayed instead.
SELECT str(-165.87,7,2) go SELECT str(-165.87,6,2) go SELECT str(165.87,6,2) go SELECT str(165.87,2,2) go ------- -165.87 ------ -165.9 ------ 165.87 -- **
STUFF inserts a string into a second string. The length argument specifies the number of characters to delete from the first string, beginning at the starting position. You can't use STUFF with TEXT or IMAGE datatypes. The STUFF syntax is as follows:
STUFF(character_string_1,starting_position,length,character_string_2)
In the following example, the string abcdef is inserted into the first string, beginning at the second character position. The abcdef string is inserted after the number of characters specified by the length argument are deleted from the first string:
SELECT stuff(`123456',2,4,'abcdef') --------- 1abcdef56
If the starting position, or length, is negative, or if the starting position is larger than the first character_string, STUFF displays a NULL string. In the following example, a NULL is the result of the code shown because the starting position is a negative value:
SELECT stuff(`wxyz',-2,3,'abcdef') (null)
If the length to delete is longer than the length of the first character string, the first character string is deleted to only the first character. In the following example, only the first character of the first character string remains after the second character string is inserted:
SELECT stuff(`123',2,3,'abc') ---- 1abc
You can use SUBSTRING to return a part of a string from a target string. The first argument can be a character or binary string, a column name, or an expression that includes a column name. The second argument specifies the position at which the substring starts. The third argument specifies the number of characters in the substring.
Like several other string-only functions, you can't use SUBSTRING with Text or Image datatypes. The SUBSTRING syntax is as follows:
SUBSTRING(character_string, starting__position,length)
In the following example, multiple SUBSTRINGs are used along with the SPACE function to separate the first name from the last name, each of which is stored in a single column of the Employees table.
SELECT substring(name,1,3),space(4),substring(name,5,5) from employees where badge=1234 ---------- ---- ----- Bob Smith
Unlike earlier examples, the following example uses a function in several SQL statements. Multiple functions are often used in stored procedures or other batch objects. See Chapter 14, "Managing Stored Procedures and Using Flow-Control Statements," for more information about the use of local variables and the SELECT statement in the following example. Like the previous example, the first name is separated from the last name with multiple spaces added between the names, all of which is done by using multiple functions.
declare @x int SELECT @x=charindex(` `,(SELECT name from employees where name='Bob Smith')) SELECT @x=@x-1 SELECT substring(name,1,@x), right(name,@x+2) from employees where badge=1234 -------------------- Bob Smith
The concatenation operator symbol (+) concatenates two or more character or binary strings, column names, or a combination of strings and columns. Concatenation is used to add one string to the end of another string. You should enclose character strings within single quotation marks. The syntax of the concatenation operator is as follows:
<expression> + <expression>
Conversion functions are used to concatenate datatypes that could not be concatenated without a change in datatype. CONVERT is one of the functions that you can use for datatype conversion. In the following example, a string constant is concatenated with the current date returned using the GETDATE date function. GETDATE is nested within CONVERT to convert it to a datatype, in this case VARCHAR, which is compatible with the string constant.
SELECT `The converted date is ` + convert(varchar(12), getdate()) ---------------------------------- The converted date is Jul 11 1994 (1 row(s) affected)
Arithmetic functions operate on numeric datatypes, such as INTEGER, FLOAT, REAL, MONEY, and SMALLMONEY. The values returned by the arithmetic functions are six decimal places. If you encounter an error while using an arithmetic function, a NULL value is returned and a warning message is displayed.
Two query processing options can be used to control the execution of statements that include arithmetic functions. The keyword for each of the two arithmetic operations is preceded by the SET keyword. You can use the ARITHABORT option to terminate a query when a function finds an error. ARITHIGNORE returns NULL when a function finds an error. If you set both ARITHABORT and ARITHIGNORE, no warning messages are returned.
There are numerous mathematical functions available in Transact-SQL (see Table 9.2).
Function | Parameters | Return |
ACOS | (float_expression) | Angle in radians whose cosine is a FLOAT value. |
ASIN | (float_expression) | Angle in radians whose sine is a FLOAT value. |
ATAN | (float_expression) | Angle in radians whose tangent is a FLOAT value. |
ATAN2 | (float_expr1,float_expr2) | Angle in radians whose tangent is float_expr1/floatexpr2. |
COS | (float_expression) | Trigonometric cosine of angle in radians. |
COT | (float_expression) | Trigonometric cotangent of angle in radians. |
SIN | (float_expression) | Trigonometric sine of angle in radians. |
TAN | (float_expression) | Trigonometric tangent of an angle in radians. |
DEGREES | (numeric_expression) | Degrees converted from radians returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL and FLOAT. |
RADIANS | (numeric_expression) | Radians converted from degrees returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. |
CEILING | (numeric_expression) | Smallest INTEGER >= expr returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. |
FLOOR | (numeric_expression) | Largest INTEGER <= expr returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. |
EXP | (float_expression) | Exponential value of expression. |
LOG | (float_expression) | Natural log of expression. |
LOG10 | (float_expression) | Base 10 log of expression. |
PI() | Value is 3.1415926535897936. | |
POWER | (numeric_expression,y) | Value of expression to power of y returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. |
ABS | (numeric_expression) | Absolute value of expression returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. |
RAND | ([integer_expression]) | Random float number between zero and one using optional int as seed. |
ROUND | (numeric_expr,integer_expr) | Rounded value to precision of integer_expr returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. |
SIGN | (numeric_expression) | One, zero, or -1 returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. |
SQRT | (float_expression) | Square root of expression. |
The following example shows the use of ABSOLUTE, RANDOM, SIGN, PI, and RANDOM within an expression:
SELECT abs(5*-15),rand(),sign(-51.23),pi(),round((10*rand()),0) ----- ------------------ ---------- ---------------------- ---- 75 0.3434553056428724 -1.0 3.141592653589793 8.0 (1 row(s) affected)
In another example of the use of mathematical functions, FLOOR and CEILING are used to return the largest and smallest integer values that are less than or equal to, or greater than or equal to, the specified value.
SELECT floor(81),ceiling(81),floor(81.45), ceiling(81.45),floor($81.45),ceiling(-81.45) -------- -------- ------------- ------------- ----------- ------- 81 81 81.0 82.0 81.00 -81.0 (1 row(s) affected)
ROUND always returns a value, even if the length is invalid. If you specify that the length is positive and longer than the digits after the decimal point in ROUND, a zero is added after the least-significant digit in the returned value. If you specify that the length is negative and greater than or equal to the digits before the decimal point, 0.00 is returned by ROUND.
The following example shows the effects of using ROUND functions on various values. In the first example, the decimal number is rounded to two decimal places. The second number is displayed as 0.00 because the length is negative.
SELECT round(81.4545,2), round(81.45,-2) ------------------------ --------------- 81.45 0.0 (1 row(s) affected)
In the following example, the first number is rounded down to three decimal places, and the second number is rounded up to a whole number because it's more than half the value of the least-significant digit.
SELECT round(81.9994,3),round(81.9996,3) ------------------------ ------------------------ 81.999 82.0 (1 row(s) affected)
In addition to PATINDEX, you can use several functions for operations on TEXT and IMAGE datatypes. You can also use relevant SET options and global variables with TEXT and IMAGE datatypes.
SET TEXTSIZE specifies the number of bytes that are displayed for data stored as TEXT or IMAGE datatypes with SELECT statements. The SET TEXTSIZE syntax is as follows:
SET TEXTSIZE n
Use n to specify the number of bytes to be displayed. You must specify the value of n in the function SET TEXTSIZE as an INTEGER. If you specify n as zero (0), the default length in bytes, up to 4K bytes, is displayed. The current setting for TEXTSIZE is stored in the global variable @@TEXTSIZE.
In the following example, the TEXTSIZE default is first used to display a table column defined as the datatype TEXT. SET TEXTSIZE is defined to two (2), and as a result, only two bytes of the table-column text are displayed. Finally, TEXTSIZE is reset to the default of 4K using a value of zero (0).
SELECT * from imagetext_table image1 ... text1 -------------------------- ... ---------------------------------- 0x31323334353637383961637a782b3d5c ... 12345678aczx+= (1 row(s) affected) set textsize 2 go SELECT text1 from imagetext_table go set textsize 0 go SELECT * from imagetext_table go text1 ------------- ... -------------------------------------- 12 (1 row(s) affected) image1 ... text1 -------------------------- ... ---------------------------------- 0x31323334353637383961637a782b3d5c ... 12345678aczx+=
(1 row(s) affected)
TEXTPTR returns a value in VARBINARY format as a 16-character binary string. The value returned is a pointer to the first database page of stored text. The text pointer is used by the SQL Server system rather than by you, although the value is accessible by using TEXTPTR.
SQL Server automatically checks if the pointer is valid when the function is used. The system checks that the return value points to the first page of text. The TEXTPTR syntax is as follows:
TEXTPTR(column_name)
READTEXT is a statement rather than a function. It is used along with the TEXT and IMAGE functions. READTEXT extracts a substring from data stored as a TEXT or IMAGE datatypes. You specify the number of bytes to include in the substring that follow an offset. The READTEXT syntax is as follows:
READTEXT [[<database.>]<owner.>]<table_name.><column_name> <text_pointer> <offset> <size>
In the following example, TEXTPTR retrieves the point to the first page of text for the one-and-only row of the table. The pointer is stored in a local variable @v. READTEXT is then used to extract a substring starting at the third byte, using an offset to skip past the first two bytes and retrieve the specified four bytes.
declare @v varbinary(16) SELECT @v=textptr(text1) from imagetext_table readtext imagetext_table.text1 @v 2 4 (1 row(s) affected) text1 --------------------------------------...---------------------------- 3456
TEXTVALID returns either zero (0) or one (1), depending on whether a specified text pointer is valid or invalid. You must include the name of the table as part of your reference to the column defined as the datatype TEXT. The TEXTVALID syntax is as follows:
TEXTVALID(`table_name.column_name', text_pointer)
In the following example, TEXTVALID determines the validity of a pointer to a data column stored as the datatype text. Recall that the output of one function can be used as the input to another function, as in the following example:
SELECT textvalid(`imagetext_table.text1',(SELECT textptr(text1) from imagetext_table)) go ----------- 1 (1 row(s) affected)
In the next example, a SELECT statement that contains a WHERE clause returns a table row. As a result, TEXTVALID returns a zero, which is an invalid value because no row column was located.
SELECT textvalid(`imagetext_table.text1',(SELECT textptr(text1) from imagetext_table where text1 like `5')) ----------- 0 (1 row(s) affected)
You often don't have to explicitly perform conversions because SQL Server automatically performs them. For example, you can directly compare a character datatype or expression with a DATETIME datatype or expression. SQL Server also converts an INTEGER datatype or expression to a SMALLINT datatype or expression when an INTEGER, SMALLINT, or TINYINT is used in an expression.
See the Chapter 6 section titled "Numeric integer Datatypes" for more information on datatypes.
Use a conversion function if you're unsure whether SQL Server will perform implicit conversions for you or if you're using other datatypes that aren't implicitly converted.
As mentioned earlier, CONVERT performs the explicit conversion of datatypes. CONVERT translates expressions of one datatype to another datatype as well as to a variety of special date formats. If CONVERT can't perform the conversion, you'll receive an error message. For example, if you attempt to convert characters contained in a column defined as a CHAR datatype to an INTEGER datatype, an error is displayed.
The CONVERT syntax is as follows:
CONVERT(<datatype> [(<length>)], <expression> [, <style>])
You can use CONVERT in SELECT and WHERE clauses or anywhere an expression can be used in a Transact-SQL statement.
Keep the following key concepts in mind when you use the CONVERT function:
In the following example, a numeric constant is converted to a CHAR datatype, a decimal constant is converted to an INT datatype, and a decimal constant is converted to a BIT datatype:
SELECT convert(char(4),1234),convert(int,12.345),convert(bit,87453.34) ---- ----------- --- 1234 12 1 (1 row(s) affected)
In the next example of using CONVERT, several table columns are converted from an INT datatype to a CHAR datatype. The attempted conversion of the same table column to a VARCHAR datatype of an inadequate length results in truncation of each column value.
SELECT badge,convert(char(4),badge),convert(varchar(2),badge) from employees badge ----------- ---- -- 3211 3211 * 6732 6732 * 4411 4411 * ...
You can use the style argument of the CONVERT function to display the date and time in different formats. You can also use the style argument as part of a CONVERT function when you convert dates and times to CHAR or VARCHAR datatypes. Table 9.3 shows the different style numbers that can be used with CONVERT.
Without Century (yy) | With Century (yyyy) | Standard | Display |
- | 0 or 100 | default | mon dd yyyy hh:miAM(orPM) |
1 | 101 | USA | mm/dd/yy |
2 | 102 | ANSI | yy.mm.dd |
3 | 103 | English/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:mi:ss | |
9 | 109 | mon dd yyyy | |
hh:mi:sssAM (or PM) | |||
10 | 110 | USA | mm-dd-yy |
11 | 111 | Japan | yy/mm/dd |
12 | 112 | ISO | yymmdd |
13 | 113 | Europe | dd mon yyyy hh:mi:ss:mmm (24h) |
14 | 114 | - | hh:mi:ss::mmm (24h) |
In the following example, the current date and time are implicitly displayed using GETDATE, and GETDATE appears within CONVERT using different style numbers.
SELECT getdate(),convert(char(12),getdate(),3),convert(char(24), getdate(),109) --------------------------- ------------ ------------------------ Jul 12 1994 1:34PM 12/07/94 Jul 12 1994 1:34:49:440 (1 row(s) affected) SELECT convert(char(24),getdate(),114),convert(char(24),getdate(),112) ------------------------ ------------------------ 13:36:45:223 19940712 (1 row(s) affected)
You can use several functions to perform operations with DATE datatypes. Use date functions to perform arithmetic operations on DATETIME and SMALLDATETIME values. Like other func- tions, date functions can be used in the SELECT or WHERE clauses, or wherever expressions can be used in Transact-SQL statements.
DATENAME returns a specified part of a date as a character string. DATENAME uses the following syntax:
DATENAME(<date part>, <date>)
DATEPART returns the specified part of a date as an integer value. DATEPART uses the following syntax:
DATEPART(<date_part>, <date>)
GETDATE returns the current date and time in SQL Server's default format for DATETIME values. Use a NULL argument with GETDATE. GETDATE uses the following syntax:
GETDATE()
DATEADD returns the value of the date with an additional date interval added to it. The return value is a DATETIME value that is equal to the date plus the number of the date parts that you specify. DATEADD takes the date part, number, and date arguments in the following syntax:
DATEADD (<date part>, <number>, <date>)
DATEDIFF returns the difference between parts of two specified dates. DATEDIFF takes three arguments, which are the part of the date and the two dates. DATEDIFF returns a signed integer value equal to the second date part, minus the first date part, using the following syntax:
DATEDIFF(<date part>, <date1>, <date2>)
Table 9.4 shows the values used as arguments for the date parts with the date functions.
Date Part | Abbreviation | Values |
Year | yy | 1753-9999 |
Quarter | 1-4 | |
Month | mm | 1-12 |
Day of Year | dy | 1-366 |
Day | dd | 1-31 |
Week | wk | 1-54 |
Weekday | dw | 1-7 (Sun-Sat) |
Hour | hh | 0-23 |
Minute | mi | 0-59 |
Second | ss | 0-59 |
Millisecond | ms | 0-999 |
The following examples show the use of several of the date functions. In the first example, the columns of a table that are defined as DATETIME and SMALLDATETIME datatypes are displayed without any functions.
SELECT * from date_table date1 date2 -------------------------- --------------------------- Jan 1 1753 12:00AM Jan 1 1900 12:00AM (1 row(s) affected)
In the following example, the keyword year is used with DATENAME to return the year with the century from a DATETIME value:
SELECT datename(year,date1) from date_table 1753 (1 row(s) affected)
In the following example, hour is used with DATENAME to return the hour from a DATETIME datatype value:
SELECT datename(hour,date1) from date_table ------------------------------ 0 (1 row(s) affected)
In the following example, month is used with DATENAME to return the number of the month from a DATETIME datatype value:
SELECT datepart(month,date1) from date_table ----------- 1 (1 row(s) affected)
In the following example, GETDATE function is used in a SELECT statement to display the current date and time:
SELECT now=getdate() now --------------------------- May 19 1994 2:00PM (1 row(s) affected)
In the following example, GETDATE is nested within DATEPART to display only the current day as part of a SELECT statement:
SELECT datepart(day,getdate()) ----------- 19 (1 row(s) affected)
In the following example, GETDATE is nested within DATENAME to display only the name of the current month as part of a SELECT statement:
SELECT datename(month,getdate()) ------------------------------ May (1 row(s) affected)
In Listing 9.3, the current date and the date stored in a DATETIME column are first displayed for reference. DATEDIFF is then used to display the number of days between the two DATETIME values.
SELECT getdate() --------------------------- May 19 1994 2:12PM (1 row(s) affected) SELECT date1 from date_table date1 --------------------------- Jan 1 1753 12:00AM (1 row(s) affected) SELECT new=datediff(day,date1,getdate()) from date_table new ----------- 88161 (1 row(s) affected)
You can use systems functions to obtain information about your computer system, user, database, and database objects. The system functions permit you to obtain information, such as the characteristics of database objects within stored procedures and in conjunction with conditional statements you can perform different operations based on the information returned.
You can use a system function, like other functions, in the SELECT and WHERE clauses of a SELECT statement, as well as in expressions. If you omit the optional parameter with some system functions, as shown in Table 9.5, information about your computer system and the current user database is returned.
Function | Parameter(s) | Information Returned |
HOST_NAME() | The name of the server computer | |
HOST_ID() | The ID number of the server computer | |
SUSER_ID | ([`login-name']) | The login number of the user |
SUSER_NAME | ([server_user_id]) | The login name of the user |
USER_ID | ([`user_name']) | The database ID number of the user |
USER_NAME | ([user_id]) | The database username of the user |
DB_NAME | ([`database_id']) | The name of the database |
DB_ID | ([`database_name']) | The ID number of the database |
GETANSINULL | ([`database_name']) | Returns 1 for ANSI nullability, 0 if ANSI nullability is not defined |
OBJECT_ID | (`object_name') | The number of a database object |
OBJECT_NAME | (object_id) | The name of a database object |
INDEX_COL | (`table_name', index_id, key_id) | The name of the index column |
COL_LENGTH | (`table_name', `column_name') | The defined length of a column |
COL_NAME | (table_id, column_id) | The name of the column |
DATALENGTH | (`expression') | The actual length of an expression of a datatype |
IDENT_INCR | (`table_or_view') | The increment (returned as numeric(@@MAXPRECISION,0)) for a column with the identity property |
IDENT_SEED | (`table_or_view') | The seed value, returned as numeric(@@MAXPRECISION,0), for a column with the identity property |
STATS_DATE | (table_id, index_id) | The date that the statistics for the index, index_id, were last updated |
COALESCE | (expression1, expression2, ... expressionN) | Returns the first non-null expression |
ISNULL | (expression, value) | Substitutes value for each NULL entry |
NULLIF | (expression1, expression2) | Returns a NULL when expression1 is NULL when expression1 is equivalent to expression2 |
In the following example, the system function HOST_ID is used to return the name of the Windows NT server system to which a user is connected:
SELECT host_name () ------------------------------ NT1 (1 row(s) affected)
In the following example, multiple system functions are used to return information about the Windows NT server system, the current database, and the current user:
SELECT host_name (),host_id (),db_name (), db_id (), suser_name () ---------- -------- ---------- -------------------------- NT1 0000005e employees 6 sa (1 row(s) affected)
NOTE: You may not have reason to use any of the system functions. You may only need to use the system functions if you're performing some administrative operation with the database. Several of the system functions require that you have access to the system tables in order to return useful information. Access to these depends on the security of your login ID.You would not usually use the system functions in the SELECT clause of a SELECT statement that displays the information on your monitor. Rather, system functions, like other functions, can be used within other functions, and the information returned is recorded in local variables or a temporary or permanent table. System functions provide information, which is usually used for advanced programming or administrative operations. Administrative operations can be performed within stored procedures, as well as in an interactive session.
For example, the system function STATS_DATE returns the date the last time that statistics were updated for an index on a table. The database administrator must periodically update the statistics for a table so that the query optimizer has valid information to use to decide whether or not to use an index for the retrieval of rows from a table. SQL Server does not automatically update the table statistics used by the query optimizer.
You can use a system function, such as STATS_DATE (as shown in the previous example), to determine if it's time to update the statistics for the indexes of a table so the query optimizer will work properly.
You can also combine the system function STATS_DATE with the functions GETDATE and DATEDIFF to return the update statistics date, the current date, and the difference between the two dates. Using these three functions and a conditional statement in a procedure, you can run the procedure periodically to determine if the statistics for a table index have been updated within some period of time, for example, a week.
If the difference between the STATS_DATE and the GETDATE is more than seven days, an UPDATE STATISTICS command should be issued. Other system functions can also be used to determine if a system operation on the database, or their objects, needs to be performed.
For example, in the Listing 9.4, the SELECT statement returns the statistics update date for two indexes on the table company:
SELECT `Index' = i.name, `Statistics Update Date' = stats_date(i.id, i.indid) from sysobjects o, sysindexes i where o.name = `company' and o.id = i.id Index Statistics Update Date ------------------------------ --------------------------- badge_index Sep 18 1995 3:24PM department_index Sep 18 1995 3:27PM (2 row(s) affected)
See the section titled "Using Date Functions" earlier in this chapter.See Chapter 14, "Managing Stored Procedures and Using Flow-Control Statements," for more information about working with stored procedures.
ISNULL is a system function that returns a string of characters or numbers in place of (NULL) when a NULL is encountered in a data-storage structure, such as a table column. The syntax of the function is as follows:
ISNULL(expression,value)
The expression is usually a column name that contains a NULL value. The value specifies a string or number to be displayed when a NULL is found. In the following example, the ISNULL function is used to return the character string `No entry' when a NULL is encountered:
SELECT ISNULL(y, `No entry') from nulltable y ----------- No entry (1 row(s) affected)
The NULLIF function returns a NULL if the two expressions are identical. If they are not, the second expression is returned. The NULLIF function is usually used with the CASE statement. In the following example, a NULL is returned for identical strings while the first parameter is returned when the strings don't match:
SELECT nullif (`same','same'),space (2),nullif (`same','different') ---- -- ---- (null) same (1 row(s) affected)
NOTE: The space function is used in the example of the NULLIF function to provide a visual separation between the values returned by the use of the function twice in the SELECT statement.
The form of the COALESCE function that uses the syntax COALESCE (expression1,expression2) is similar to the NULLIF statement. Unlike the NULLIF statement, the COALESCE statement, with two parameters, returns expression2 when a NULL is returned and returns expression1 if NOT NULL is encountered.
You can also use COALESCE with more than two parameters. COALESCE returns the first non-null expression in the list of parameters when no NULL is used. If no non-null values are present, when COALESCE is used with more than two parameters, the function returns a NULL.
NOTE: The COALESCE function is designed for use in a CASE statement, which is discussed in the chapter on stored procedures. Please consult Chapter 14 on stored procedures for additional information on the COALESCE function.
See the Chapter 14 section titled "Using CASE Expressions."
Niladic functions return a user or timestamp value, which is automatically placed in the row of a table when the value is omitted from an INSERT or UPDATE statement. Niladic functions are defined as part of a DEFAULT constraint in a CREATE or ALTER TABLE statement. You can use any of the following niladic functions:
USER
CURRENT_USER
SESSION_USER
SYSTEM_USER
CURRENT_TIMESTAMP APP_NAME
The niladic functions USER, CURRENT_USER, and SESSION_USER all return the database username of the user executing an INSERT or UPDATE statement. The function SYSTEM_USER returns the user's login ID. CURRENT_TIMESTAMP returns the current date and time in the same form as the GETDATE function. APP_NAME returns the program name for the current session if one has been set.
Niladic functions cannot be used outside the DEFAULT CONSTRAINT of a CREATE or ALTER TABLE statement. For example, you cannot use the niladic functions in the SELECT clause of a SELECT statement.
See the Chapter 13 section titled "Managing and Using Rules, Constraints, and Defaults," for more information on setting up these options.
Remember, the goal of your implementation needs to be to return the smallest number of rows possible in each query you submit to the server. To that end, the set of SQL functions you use can be very helpful.
In many cases, you'll find that if you're working with information from the database in your application, you can consider carefully how you're using it-- what the end-goal is for the information. If you're trying, for example, to analyze a set of numbers and then use the result to show the application user, consider using server-side functions-- those outlined in this chapter--to help mold the information as you need it.
When you use these functions, you create a true client-server application, one that allows the server to do the database-information manipulation that it's so good at. You also optimize things for the client. Because the client won't have to manipulate the information returned from the database, response time to the user will be faster.
This directly impacts systems that you convert from older database types. For example, in one project that was completed, the database was converted to SQL Server from Btrieve&tm;. In Btrieve, the data manipulation, relationships, and so on are all managed by the application.
When the database was converted, it was necessary to redesign the system to allow the back-end system a more active role in the manipulation of the information in the system. This meant removing some functionality from the client side and implementing it on the server. Of course this led to code changes in the application, potentially impacting the schedule to cut over the application.
One approach you may want to take with legacy systems is to first convert the database, leaving the processing assignments between the client and server side as they were in the original system. After the system is online, and you've worked out the kinks in the data conversion, you can begin writing the stored procedures to automate the processes on the server.
Because you'll not be impacting the database, only providing a new way to get information from it, the application will still work during the process of creating the stored procedures. Then, when you're ready on the database side, you can go back into the code and make the changes, one by one, to use the stored procedures instead of the client-side processing. As you're doing a controlled cut-over on the client application, you can test each change carefully.
At the same time, you're controlling the risk to the client application by only implementing a single new function at a time.
In this chapter you've learned the use of various functions that return information about your system and characteristics of SQL Server. In addition, you learned how to use functions to perform operations on table data. Functions can be used both in an interactive session and in stored procedures.
For information about SELECTed aspects of the topics mentioned in this chapter, review the following chapters:
© Copyright, Macmillan Computer Publishing. All rights reserved.