Using Functions

SQL Server provides a number of functions you can use to get values from the database. For example, you can use the COUNT() function to get the number of rows in a table. The various functions are split into the categories shown in Table 4.2.

Table 4.2: FUNCTIONS

FUNCTION CATEGORY

DESCRIPTION

Aggregate

Return information based on one or more rows in a table.

Mathematical

Perform calculations.

String

Perform string manipulations.

Date and time

Work with dates and times.

System

Return information on SQL Server.

Configuration

Return information on the configuration of the server.

Cursor

Return information on cursors.

Metadata

Return information on the database and the various database items, such as tables.

Security

Return information on the database users and roles.

System statistical

Return statistical information on SQL Server.

Text and image

Perform text and image manipulations.

You'll learn about the first five functions in the following sections. The other categories of functions are beyond the scope of this book, as they are of primary interest to database administrators. You can learn about those functions in the SQL Server Online Books documentation.

Using Aggregate Functions

Earlier, you saw the use of the COUNT() aggregate function to get the number of rows. COUNT() and some other aggregate functions you can use with SQL Server are listed in Table 4.3. The expression you may pass to the aggregate functions is typically a single column, but it can also be a calculated field. ALL means that the function is applied to all the column values, while DISTINCT means that the function is applied only to unique values. ALL is the default.

Table 4.3: AGGREGATE FUNCTIONS

FUNCTION

DESCRIPTION

AVG([ ALL | DISTINCT ] expression)

Returns the average of the values in a group.

COUNT([ ALL | DISTINCT ] expression ] | *)

Returns the number of rows in a group. COUNT() returns an int data type value.

COUNT_BIG([ ALL | DISTINCT ] expression} | *)

Returns the number of values in a group.COUNT_BIG()returns a bigint data type value

MAX([ ALL | DISTINCT ] expression)

Returns the highest value.

MIN([ ALL | DISTINCT ] expression)

Returns the lowest value.

SUM([ ALL | DISTINCT ] expression)

Returns the sum of any non-null values. SUM() can be used only with numeric expressions.

STDEV(expression)

Returns the standard deviation for all the values.

STDEVP(expression)

Returns the standard deviation for the population of all the values.

VAR(expression)

Returns the variance for all the values.

VARP(expression)

Returns the variance for the population of all the values.

Let's consider examples that use some of the aggregate functions.

You use the AVG() function to get the average value. For example, the following statement gets the average of the UnitPrice column of the Products table using the AVG() function:

 SELECT AVG(UnitPrice) FROM Products; 

This example returns 28.8663. Since ALL is the default used with functions, this example uses every row in the Products table when performing the calculation. If you wanted to just use unique values in the calculation, then you use the DISTINCT option, as shown in the following example:

 SELECT AVG(DISTINCT UnitPrice) FROM Products; 

This example returns 31.4162, slightly higher than the previous result because only unique values are used this time.

In addition to passing a column to a function, you can also pass a calculated field. For example, the following statement passes the calculated field UnitPrice * 1.20 to the AVG() function:

 SELECT AVG(UnitPrice * 1.20) FROM Products; 

This example returns 34.639636; the average after the UnitPrice values have been increased 20 percent.

You can limit the rows passed to a function using a WHERE clause. For example, the following SELECT statement calculates the average UnitPrice value for the rows with a CategoryID of 1:

 SELECT AVG(UnitPrice) FROM Products WHERE CategoryID = 1; 

This example returns 37.9791.

You can combine a function with a GROUP BY clause to perform a calculation on each group of rows. For example, the following SELECT statement calculates the average UnitPrice value for each block of rows grouped by CategoryID:

 SELECT AVG(UnitPrice) FROM Products GROUP BY CategoryID; 

Figure 4.2 shows the results of this SELECT statement.


Figure 4.2: Using the AVG() function to compute the average value of the UnitPrice column

You can also supply a HAVING clause to eliminate groups used in a SELECT statement. For example, the following statement adds a HAVING clause to the previous example to eliminate the groups that have an average value greater than 50:

 SELECT AVG(UnitPrice) FROM Products GROUP BY CategoryID HAVING AVG(UnitPrice) > 50; 

This example returns 54.0066.

Let's take a look at some of the other aggregate functions. You get the total number of rows using the COUNT() function. For example, the following statement gets the total number of rows in the Products table using the COUNT() function:

 SELECT COUNT(*) FROM Products; 

This example returns 77.

You use the MAX() and MIN() functions to get the maximum and minimum values. For example, the following statement uses these functions to get the maximum and minimum UnitPrice:

 SELECT MAX(UnitPrice), MIN(UnitPrice) FROM Products; 

This example returns 263.5000 and 2.5000 for the respective maximum and minimum values.

You use the SUM() function to get the total of any non-null values. For example, the following statement gets the sum of the UnitPrice column values for each group of rows using the SUM() function:

 SELECT SupplierID, SUM(UnitPrice) AS SumUnitPrice FROM Products GROUP BY SupplierID; 

The GROUP BY clause of this example returns one row for each block of rows with identical SupplierID column values. The SUM() function then adds up the UnitPrice column values for all the rows within each block and returns a single value. For example, SUM() returns 47.0000 for the group where the SupplierID is 1. This is the sum of the UnitPrice column values for all the rows where the SupplierID is 1. Similarly, SUM() returns 81.4000 where the SupplierID is 2, and so on. The AS clause in this example names the results returned by the SUM() function as SumUnitPrice.

Figure 4.3 shows the results of this SELECT statement.


Figure 4.3: Using the SUM() function to compute the total of the UnitPrice column

Using Mathematical Functions

The mathematical functions allow you to perform numerical operations, such as getting the absolute value of a number. Table 4.4 lists the mathematical functions available in SQL Server. The expression you may pass to the mathematical functions is typically a single column or value, but it can also be a calculated field.

Table 4.4: MATHEMATICAL FUNCTIONS

FUNCTION

DESCRIPTION

ABS(expression)

Returns the absolute value of expression. This is always a positive number.

ACOS(expression)

Returns the arccosine of expression.

ASIN(expression)

Returns the arcsine of expression.

ATAN(expression)

Returns the arctangent of expression.

ATN2(expression1, expression2)

Returns the arctangent of the angle between expression1 and expression2.

CEILING(expression)

Returns the smallest integer greater than or equal to expression.

COS(expression)

Returns the cosine of expression.

COT(expression)

Returns the cotangent of expression.

DEGREES(expression)

Converts the supplied angle in radians to an angle in degrees.

EXP(expression)

Returns the exponential value of expression.

FLOOR(expression)

Returns the largest integer less than or equal to expression.

LOG(expression)

Returns the natural logarithm of expression.

LOG10(expression)

Returns the base-10 logarithm of expression.

PI()

Returns the mathematical constant Pi.

POWER(expression, y)

Returns the value of expression raised to the power y.

RADIANS(expression)

Converts the supplied angle in degrees to an angle in radians.

RAND([expression])

Returns a random floating-point number between 0 and 1. The expression is an optional seed value that you may use to generate the random number.

ROUND(expression, length [, function])

Returns the value of expression rounded or truncated to the number of decimal placed specified by length. The optional function is used to specify the type of operation to perform: 0 (the default) rounds the number, and any other value truncates the number.

SIGN(expression)

Returns 1, 0, or -1 depending on the sign of expression. Returns 1 for a positive number, 0 for zero, or -1 for a negative number.

SIN(expression)

Returns the sine of expression.

SQUARE(expression)

Returns the square of expression.

SQRT(expression)

Returns the square root of expression.

TAN(expression)

Returns the tangent of expression.

Let's consider examples that use some of the mathematical functions. You use the ABS() function to get the absolute value. The following example returns 10 and 15:

 SELECT ABS(-10), ABS(15); 

You use the ACOS(), ASIN(), and ATAN() functions to get the arccosine, arcsine, and arctangent of a number. The following example returns 0.0, 1.5707963267948966, and 0.78539816339744828:

 SELECT ACOS(1), ASIN(1), ATAN(1); 

You use the CEILING() function to get the smallest integer greater than or equal to the value passed to it. The following example returns 2 and -1:

 SELECT CEILING(1.4), CEILING(-1.4); 

You use the FLOOR() function to get the largest integer less than or equal to the value passed to it. The following example returns 1 and -2:

 SELECT FLOOR(1.4), FLOOR(-1.4); 

You use the PI() function to get the mathematical constant Pi. The following example returns 3.1415926535897931:

 SELECT PI(); 

You use the POWER() function to get the value of a number raised to a specified power. The following example returns 8:

 SELECT POWER(2, 3); 

You use the ROUND() function to get the value of a number rounded or truncated to a specified length. The following example returns 1.23500, which is 1.23456 rounded to three decimal places:

 SELECT ROUND(1.23456, 3); 

The next example passes a non-zero number as the third parameter to ROUND(), which indicates that the number is to be truncated rather than rounded, as was done in the previous example:

 SELECT ROUND(1.23456, 3, 1); 

This example returns 1.23400, which is 1.23456 truncated to three decimal places.

You use the SQUARE() function to get the square of a number. The following example returns 16.0:

 SELECT SQUARE(4); 

You use the SQRT() function to get the square root of a number. The following example returns 4.0:

 SELECT SQRT(16); 

Using String Functions

The string functions allow you to manipulate strings. For example, you can replace specified characters in a string. Table 4.5 lists the string functions available in SQL Server.

Table 4.5: STRING FUNCTIONS

FUNCTION

DESCRIPTION

ASCII(charExpression)

Returns the ASCII code for the leftmost character of charExpression.

CHAR(intExpression)

Returns the character that corresponds to the ASCII code specified by intExpression.

CHARINDEX (charExpression1, charExpression2 [, start ])

Returns the position of the characters specified by charExpression1 in charExpression2, starting at the optional position specified by start.

DIFFERENCE (charExpression1, charExpression2)

Returns the difference between the SOUNDEX values of the two character expressions. You use the SOUNDEX code to evaluate the phonetic similarity of two strings. The returned value is between 0 and 4; 4 indicates that the two expressions are phonetically identical.

LEFT(charExpression, intExpression)

Returns the leftmost characters specified by intExprssion from charExpression.

LEN(charExpression)

Returns the number of characters in charExpression.

LOWER(charExpression)

Converts the characters in charExpression to lowercase and returns those characters.

LTRIM(charExpression)

Removes any leading spaces from the start of charExpression and returns the remaining characters.

NCHAR(intExpression)

Returns the Unicode character with the code specified by intExpression.

PATINDEX('%pattern%', charExpression)

Returns the starting position of the first occurrence of pattern in charExpression. If pattern is not found then zeros are returned.

REPLACE (charExpression1, charExpression2, charExpression3)

Replaces all occurrences of charExpression2 in charExpression1 with charExpression3.

QUOTENAME ('charString' [ , 'quoteChar' ])

Returns a Unicode string with the delimiters specified by quoteChar added to make charString a valid delimited identifier.

REPLICATE (charExpression, intExpression)

Repeats charExpression a total of intExpression times.

REVERSE(charExpression)

Reverses the characters in charExpression and returns those characters.

RIGHT(charExpression, intExpression)

Returns the rightmost characters specified by intExprssion from charExpression.

RTRIM(charExpression)

Removes any trailing spaces from the end of charExpression and returns the remaining characters.

SOUNDEX(charExpression)

Returns the four-character SOUNDEX code. You use this code to evaluate the phonetic similarity of two strings.

SPACE(intExpression)

Returns a string of repeated spaces for a total specified by intExpression.

STR(floatExpression [ , length [ , decimal ] ])

Converts the number specified by floatExpression to characters; length specifies the total number of characters you want to see (including digits and spaces, plus the positive or negative sign and decimal point); decimal specifies the number of digits to the right of the decimal point. The number is rounded if necessary.

STUFF (charExpression1, start, length, charExpression2)

Deletes characters from charExpression1, starting at the position specified by start for a total of length characters, and then inserts the characters specified by charExpression2.

SUBSTRING(expression, start, length)

Returns part of a character, binary, text, or image expression.

UNICODE('nCharExpression')

Returns the Unicode value for the first character of the nchar or nvarchar expression nCharExpression.

UPPER(charExpression)

Converts the characters in charExpression to uppercase and returns those characters.

Let's consider examples that use some of the string functions.

You use the ASCII() function to get the ASCII code for the leftmost character of the supplied character expression. The following example returns 65 and 97:

 SELECT ASCII('A'), ASCII('a'); 

You use the CHAR() function to get the character that corresponds to the ASCII code of the supplied integer expression. The following example returns A and a:

 SELECT CHAR(65), CHAR(97); 

You use the CHARINDEX() function to get the position of characters. The following example returns 16, which is the position where the word ten starts:

 SELECT CHARINDEX('ten', 'Four-score and ten years'); 

You use the DIFFERENCE() function to obtain the difference between the SOUNDEX values of two character expressions. The following example returns 4, indicating that Brown and Browne are phonetically identical:

 SELECT DIFFERENCE('Brown', 'Browne'); 

You use the LEFT() function to obtain the leftmost characters of a character expression. The following example returns Four-score, which are the 10 leftmost characters of Four-score and ten years:

 SELECT LEFT('Four-score and ten years', 10); 

You use the RIGHT() function to obtain the rightmost characters of a character expression. The following example returns years, which are the five rightmost characters of Four-score and ten years:

 SELECT RIGHT('Four-score and ten years', 5); 

You use the LEN() function to obtain the digits in a character expression. The following example returns 24:

 SELECT LEN('Four-score and ten years'); 

You use the LOWER() function to obtain the lowercase version of a character expression. The following example returns four-score and ten years:

 SELECT LOWER('FOUR-SCORE AND TEN YEARS'); 

You use the UPPER() function to obtain the uppercase version of a character expression. The following example returns FOUR-SCORE AND TEN YEARS:

 SELECT UPPER('four-score and ten years'); 

You use the LTRIM() and RTRIM() functions to remove any spaces from the left and right of a character expression. The following example returns FOUR-SCORE and AND TEN YEARS (spaces removed):

 SELECT LTRIM(' FOUR-SCORE'), RTRIM('AND TEN YEARS '); 

You use the STR() function to convert a numeric value to a string consisting of numbers. The first parameter is the number to convert, the second is the total number of characters you want in your string, and the third is the number of digits after the decimal point. The following example returns 123.46:

 SELECT STR(123.456, 6, 2); 

The number 123.456 is converted to a string of six characters, with two digits after the decimal point, and rounded.

You use the STUFF() function to replace characters. The first parameter is the string you want to replace characters in, the second is the starting position, the third is the total number of characters, and the fourth is the set of characters to insert. The following example returns Five-score and ten:

 SELECT STUFF('Four-score and ten', 1, 4, 'Five'); 

Four is replaced with Five.

You use the SUBSTRING() function to obtain part of a string. The first parameter is the string, the second is the starting position, and the third is the total number of characters. The following example returns Four:

 SELECT SUBSTRING('Four-score and ten', 1, 4); 

You use the UNICODE() function to obtain the Unicode value for the first character. The following example returns 65 and 97:

 SELECT UNICODE('A'), UNICODE('a'); 

Using Date and Time Functions

The date and time functions allow you to manipulate dates and times. For example, you can add a number of days to a given date. Table 4.6 lists the date and time functions available in SQL Server.

Table 4.6: DATE AND TIME FUNCTIONS

FUNCTION

DESCRIPTION

DATEADD(interval, number, date)

Returns a datetime that is the result of adding the specified number of interval units to date. Valid intervals include year, quarter, month, dayofyear, day, week, hour, minute, second, and millisecond.

DATEDIFF (interval, startDate, endDate)

Returns the difference between startDate and endDate, with the difference calculated in interval units (year, quarter, and so on).

DATENAME(interval, date)

Returns a character string that represents the name of interval part of date.

DATEPART(interval, date)

Returns an integer that represents the interval part of date.

DAY(date)

Returns an integer that represents the day part of date.

GETDATE()

Returns a datetime containing the current system date.

GETUTCDATE()

Returns a datetime containing the current system date as UTC time (Universal Time Coordinate or Greenwich Mean Time). The UTC time is derived from the current local time and the system time-zone setting.

MONTH(date)

Returns an integer that represents the month part of date.

YEAR(date)

Returns an integer that represents the year part of date.

Let's consider examples that use some of the date and time functions.

You use the DATEADD() function to add a number of intervals to a date. The following example adds two days to the date 12/20/2003 and returns 2003-12-22 00:00:00.000:

 SELECT DATEADD(day, 2, '12/20/2003'); 

You use the DATEDIFF() function to obtain the difference between two dates. The following example obtains the difference between 12/20/2003 and 12/22/2003 in days and returns 2 days:

 SELECT DATEDIFF(day, '12/20/2003', '12/22/2003'); 

You use the DATENAME() method to obtain a character string that represents the interval part of a date. The following example gets the month name of 12/20/2003 and returns December:

 SELECT DATENAME(month, '12/20/2003'); 

You use the DATEPART() method to obtain an integer that represents the interval part of a date. The following example gets the month number of 12/20/2003 and returns 12:

 SELECT DATEPART(month, '12/20/2003'); 

You use the DAY() function to obtain an integer that represents the day part of a date. The following example gets the day number of 12/20/2003 and returns 20:

 SELECT DAY('12/20/2003'); 

You use the MONTH() function to obtain an integer that represents the month part of a date. The following example gets the month number of 12/20/2003 and returns 12:

 SELECT MONTH('12/20/2003'); 

You use the YEAR() function to obtain an integer that represents the year part of a date. The following example gets the year number of 12/20/2003 and returns 2003:

 SELECT YEAR('12/20/2003'); 

You use the GETDATE() function to obtain the current system date. The following example returns 2002-07-16 12:59:50.823:

 SELECT GETDATE(); 

You use the GETUTCDATE() function to obtain the current system date as UTC time. The following example returns 2002-07-16 20:02:18.123:

 SELECT GETUTCDATE(); 

Using System Functions

The system functions allow you to manipulate and obtain information about values, objects, and settings in SQL Server. For example, you can convert a value in one type to another type. Table 4.7 lists some of the system functions available in SQL Server.

Table 4.7: SYSTEM FUNCTIONS

FUNCTION

DESCRIPTION

CONVERT(dataType expression [, style [(length )], ])

Converts the value in expression to the type specified by dataType. If you are converting to an nchar, nvarchar, char, varchar, binary, or varbinary type, you can also specify an optional length, which specifies the length of the new value. You can use the optional style when

  • Converting datetime or smalldatetime data to character data; style is the format for the date and time.

  • Converting float, real, money, or smallmoney data to character data; style is the string format for the number. You can look up the details for style option in the SQL Server Books Online documentation.

COALESCE(expression1 [ , ... expressionN])

Returns the first non-null expression in the list of expressions.

DATALENGTH(expression).

Returns the number of bytes used to represent expression.

@@ERROR

Returns the error number for the last T-SQL statement that was executed.

@@IDENTITY

Returns the last inserted identity value.

ISDATE(expression)

Returns 1 when expression is a valid date, otherwise 0 is returned.

ISNULL(expression, replacementValue)

If expression is null, then replacementValue is returned, otherwise expression is returned.

ISNUMERIC(expression).

Returns 1 when expression is a valid number, otherwise 0 is returned.

NEWID()

Returns a unique value of the uniqueidentifier type.

NULLIF(expression1, expression2)

Returns a null if expression1 equals expression2.

@@ROWCOUNT

Returns the number of rows affected by the last T-SQL statement that was executed.

@@TRANCOUNT

Returns the number of active transactions for the currentconnection to the database.

Let's consider examples that use some of the system functions.

You use the CONVERT() function to convert a value from one type to another. The following example converts the number 123.456 to an nvarchar and returns 123.456:

 SELECT CONVERT(nvarchar, 123.456); 

You use the COALESCE() function to obtain the first non-null expression in a list. The following example returns 123.456:

 SELECT COALESCE(null, null, 123.456, null); 

You use the DATALENGTH() function to obtain the number of bytes used to represent an expression. The following example displays the number of bytes used to represent the value stored in the CompanyName column of the Customers table for the row where CustomerID equals ALFKI:

 SELECT DATALENGTH(CompanyName), CompanyName FROM Customers WHERE CustomerID = 'ALFKI'; 

This example returns 38 and Alfreds Futterkiste, which contains 19 letters. Each letter is stored in 2 bytes, and the 19-letters string therefore takes up 38 bytes (2 * 19).

You use the ISDATE() function to determine if an expression is a valid date. ISDATE() returns 1 when the expression is a valid date, otherwise it returns 0. The following example returns 1 and 0:

 SELECT ISDATE('12/20/2004'), ISDATE(1234); 

You use the ISNUMERIC() function to determine if an expression is a valid number. The following example returns 1 and 0:

 SELECT ISNUMERIC(1234), ISNUMERIC('abc'); 

You use the ISNULL() function to replace a null value with another value. The following example returns 10 and 20:

 SELECT ISNULL(null, 10), ISNULL(20, 10); 




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net