Conversion Functions

Data type conversion can be performed using the CAST() and CONVERT() functions. For most purposes, these two functions are redundant and reflect the evolutionary history of the SQL language. The functionality may be similar, however the syntax is different. Not all values can be converted to other data types. Generally speaking, any value that can be converted can be done so with a simple function call.

CAST()

The CAST() function accepts one argument, an expression, which includes both the source value and a target data type separated by the word AS. Here is an example, using the literal string '123' converted to an integer:

 SELECT CAST('123' AS Int) 

The return value will be the integer value 123. However, what happens if you try to convert a string representing a fractional value to an integer?

 SELECT CAST('123.4' AS Int) 

Neither the CAST() nor the CONVERT() functions will do any guessing, rounding, or truncation for you. Because the value 123.4 can't be represented using the Int data type, the function call produces an error:

Server: Msg 245, Level 16, State 1, Line 1  Syntax error converting the varchar value '123.4' to a column of data type int. 

If you need to return a valid numeric equivalent value, you must use a data type equipped to handle the value. There are a few that would work in this case. If you use the CAST() function with your value to a target type of Decimal, you can specifically define the precision and scale for the decimal value. In this example, the precision and scale are 9 and 2, respectively. Precision is the total number of digits that can be stored to both the left and right of the decimal point. Scale is the number of digits that will be stored to the right of the decimal point. This means that the maximum whole number value would be 9,999,999 and the smallest fractional number would be .01.

 SELECT CAST('123.4' AS Decimal(9,2)) 

The Decimal data type displays the significant decimal positions in the results grid:

123.40

The default values for precision and scale are 18 and 0 respectively. Without providing values for precision and scale of the Decimal type, SQL Server effectively truncates the fractional part of the number without causing an error.

 SELECT CAST('123.4' AS Decimal) 

The result looks like an integer value:

123

Applying data type conversions to table data is very easy to do. The next example uses the Employee table, and starts with the following query:

 SELECT FirstName, LastName, DepartmentID, ShiftID, BirthDate  FROM Employee ORDER BY LastName 

Ordering by the last name just gives you some variety in the DepartmentID and ShiftID column values in the first page of the results grid. The results are shown in Figure 6-2.

Figure 6-2:

You'd like to create a new value, made up of the DepartmentID and ShiftID separated by a hyphen. If you use the following expression, you don't get the result you're looking for. In fact, the hyphen isn't even included in the resulting value.

 SELECT DepartmentID + '-' + ShiftID FROM Employee 

The problem with this expression is that you're trying to concatenate an integer (the DepartmentID), a character value (the hyphen), and another integer (the ShiftID). Apparently, the query engine perceives the hyphen to be a mathematical operator, rather than a character. Regardless of the outcome, you need to fix the expression and make sure you are working with the appropriate data types. This expression makes the necessary type conversions:

 SELECT CAST(DepartmentID AS VarChar(5)) + '-' + CAST(ShiftID AS VarChar(5)) FROM Employee 

Converting the integer values to the VarChar type makes these character values without adding any extra spaces. These values are combined with the hyphen using the plus sign to concatenate string values rather than adding and subtracting the previous numeric values. Now add the first name, last name, and birth date columns:

 SELECT FirstName , LastName , CAST(DepartmentID AS VarChar(5)) + '-’ , BirthDate FROM Employee ORDER BY LastName 

The results are shown in Figure 6-3.

Figure 6-3:

Don't worry about the BirthDate column. I have plans for it in the next section. As you see, the DepartmentID and ShiftID are combined as you wanted them to be.

CONVERT()

For simple type conversion, the CONVERT() function does the same thing as the CAST() function, only with different syntax. It requires two arguments: the first for the target data type and the second for the source value. Here are a couple of quick examples similar to those used in the preceding section:

 SELECT CONVERT(INT, '123') SELECT CONVERT(Decimal(9,2), '123.4') 

The CONVERT() function also has some enhanced features making it useful for returning formatted string values. Date values can be formatted in a variety of ways. There are 28 predefined date formats to accommodate international and special-purpose date and time output. The following table shows how these break down.

Format Number	Year Digits	Hour Format	Description	Example
0	2	12	Default	Apr 25 2005 1:05PM
1	2		US	04/25/05
2	2		ANSI	05.04.25
3	2		UK/French	25/04/05
4	2		German	25.04.05
5	2		Italian	25-04-05
6	2		Custom - Date Only	25 Apr 05
7	2		Custom - Date Only	Apr 25, 05
8		24	Custom - Time Only	13:05:35
9	4	12	Default, milliseconds	Apr 25 2005 1:05:35:123PM
10	2		US	04-25-05
11	2		Japan	05/04/25
12	2		ISO	050425
13	4	24	Europe	25 Apr 2005 13:05:35:123
14		24	Custom Time, milliseconds	13:05:35:123
100	4	12	Default	Apr 25 2005 1:05PM
101	4		US	04/25/2005
102	4		ANSI	2005.04.25
103	4		UK/French	25/04/2005
104	4		German	25.04.2005
105	4		Italian	25-04-2005
106	4		Custom - Date Only	25 Apr 2005
107	4		Custom - Date Only	Apr 25, 2005
108		24	Custom - Time Only	13:05:35
109	4	12	Default, milliseconds	Apr 25 2005 1:05:35:123PM
110	4		US	04-25-2005
111	4		Japan	2005/04/25
112	4		ISO	20050425
113	4	24	Europe	25 Apr 2005 13:05:35:123
114		24	Custom Time, milliseconds	13:05:35:123

The third argument to this function is optional and accepts the format number integer value. The examples provided in the grid apply to the DateTime data type. When converting from the SmallDateTime data type, the formatting remains the same but some elements will display 0. Here are a few examples of some related script along with formatted date output:

 SELECT 'Default Date: ' + CONVERT(VarChar(50), GETDATE(), 100) Default Date: Apr 25 2005 1:05PM     SELECT 'US Date: ' + CONVERT(VarChar(50), GETDATE(), 101) US Date: 04/25/2005     SELECT 'ANSI Date: ' + CONVERT(VarChar(50), GETDATE(), 102) ANSI Date: 2005.04.25     SELECT 'UK/French Date: ' + CONVERT(VarChar(50), GETDATE(), 103) UK/French Date: 25/04/2005     SELECT 'German Date: ' + CONVERT(VarChar(50), GETDATE(), 104) German Date: 25.04.2005

Format numbers 0, 1, and 2 also apply to numeric types and affect the format of decimal and thousand separators. The effect is different for different data types. In general, using the format number 0 (or no value for this argument) returns a formatted value in the data type's most native form. Using 1 or 2 generally displays a more detailed or precise value. The following example uses 0:

 DECLARE @Num Money SET @Num = 1234.56 SELECT CONVERT(VarChar(50), @Num, 0) 

It returns the following:

1234.56

Using 1 returns the following:

1,234.56

And using 2 returns the following:

1234.5600

This example does the same thing with a Float type:

 DECLARE @Num Float SET @Num = 1234.56 SELECT CONVERT(VarChar(50), @Num, 2) 

Using the value 0 doesn't change the format from what you've provided but using 1 or 2 returns the number expressed in scientific notation, the latter using 15 decimal positions:

1.234560000000000e+003

The STR() Function

This is a quick-and-easy conversion function that converts a numeric value to a string. The function accepts three arguments: the numeric value, the overall length, and the number of decimal positions. If the integer part of the number and decimal positions is shorter than the overall length, the result is left-padded with spaces. In this first example, the value (including the decimal) is five characters long. I've made it a point to show the results in the grid so you can see any left padding. This call asks for an overall length of eight characters with four decimal positions:

 SELECT STR(123.4, 8, 4) 

The result has the decimal value right-filled with 0s, as shown in Figure 6-4.

Figure 6-4:

Here I'm passing in a ten-character value and asking for an eight-character result, with four decimal positions:

 SELECT STR(123.456789, 8, 4) 

The result must be truncated to meet my requirements. The STR() function rounds the last digit, as shown in Figure 6-5.

Figure 6-5:

Now I'll pass in the number 1 for the value and ask for a six-character result with four decimal positions. In this case, the STR() function right-fills the decimal value with zeros, as shown in Figure 6-6.

Figure 6-6:

 SELECT STR(1, 6, 4) 

However, if I specify an overall length greater than the length of the value, decimal point, and the decimal value, the result will be left-padded with spaces, as shown in Figure 6-7.

Figure 6-7:

 SELECT STR(1, 12, 4) 

Cursor Functions and Variables

Chapter 10 discusses the use of cursors along with some of the pros and cons of using this technique. The short version of this topic is that cursors can provide the ability to process multiple rows of data, one row at a time, in a procedural loop. This ability comes at a cost when compared with more efficient, set-based operations. One function and two global variables are provided to help manage cursor operations.

The CURSOR_STATUS() Function

This function returns an integer indicating the status of a cursor-type variable passed into the function. A number of different types of cursors can affect the behavior of this function. For simplicity, the return value will typically be one of those listed in the following table.

Return Value	Description
1	Cursor contains one or more rows (dynamic cursor contains 0 or more rows)
0	Cursor contains no rows
-1	Cursor is closed
-2	Cursor is not allocated
-3	Cursor doesn't exist

@@CURSOR_ROWS

This variable is an integer value representing the number of rows in the cursor open in the current connection. Depending on the cursor type, this value may or may not represent the actual number of rows in the result set.

@@FETCH_STATUS

This variable is a flag that indicates the state of the current cursor pointer. It is used primarily to determine whether a row still exists and when you have reached the end of the result set after executing a FETCH NEXT statement.

Date Functions

These functions are used for working with DateTime and SmallDateTime type values. Some are used for parsing the date and time portions of a date value and for comparing and manipulating date/ time values.

The DATEADD() Function

The DATEADD() function adds a specific number of date unit intervals to a date/time value. For example, to determine the date 90 days after April 29, 1988, the following statement is used:

 SELECT DATEADD(Day, 90, '4-29-1988') 

The answer is as follows:

1988-07-28 00:00:00.000 (July 28, 1988) 

Any of the values in the following table can be passed in the Interval argument.

Interval	Interval Argument Values
Year	Year, yyyy, yy
Quarter	quarter, qq, q
Month	Month, mm, m
Day of the year	dayofyear, dy, y
Day	Day, dd, d
Week	Week, wk, ww
Hour	Hour, hh
Minute	minute, mi, n
Second	second, ss, s
Millisecond	millisecond, ms

Using the same date as before, here are some more examples. This time, I'll include the time as well. The results are on the following line:

18 years later:

 SELECT DATEADD(year, 18, '4-29-1988 10:30 AM')  2006-04-29 10:30:00.000

18 years before:

 SELECT DATEADD(yy, -18, '4-29-1988 10:30 AM')  1970-04-29 10:30:00.000

9,000 seconds after:

 SELECT DATEADD(second, 9000, '4-29-1988 10:30 AM')  1988-04-29 13:00:00.000

9,000,000 milliseconds before:

 SELECT DATEADD(mi, -9000000, '4-29-1988 10:30 AM')  1971-03-20 10:30:00.000

I can combine the CONVERT()and the DATEADD() functions to format a return date value nine months before September 8, 1989:

 SELECT CONVERT(VarChar(20), DATEADD(m, -9, '9-8-1989'), 101)  12/08/1988 

This returns a variable-length character value, a little easier to read than the default dates you saw in the previous results. This is a nested function call where the results from the DATEADD() function (a DateTime type value) are fed to the value argument of the CONVERT() function.

The DATEDIFF() Function

I think of the DATEADD() and DATEDIFF() functions as cousins — sort of like multiplication and division. There are four elements in this equation: the start date, the interval (date unit), the difference value, and the end date. If you have three, you can always figure out what the fourth one is. I use a start date, an integer value, and interval unit with the DATEADD() function to return the end date value relative to a starting date. The DATEDIFF() function returns the difference integer value if I provide the start and end dates and interval. Do you see the relationship?

To demonstrate, I simply choose any two dates and an interval unit as arguments. The function returns the difference between the two dates in the interval unit provided. I want to know what the difference is between the dates 9-8-1989 and 10-17-1991 in months:

 SELECT DATEDIFF(month, '9-8-1989', '10-17-1991') 

The answer is 25 months. How about the difference in days?

 SELECT DATEDIFF(day, '9-8-1989', '10-17-1991') 

It's 769 days.

How about the difference in weeks between 7-2-1996 and 8-4-1997?

 SELECT DATEDIFF(week, '7-2-1996', '8-4-1997') 

57 weeks.

You can even figure out how old you are in seconds:

 DECLARE @MyBirthDate DateTime SET @MyBirthDate = '3-24-1967'  SELECT DATEDIFF(ss, @MyBirthDate, GETDATE()) 

Someone is over 1.3 billion seconds old!

I apply this function to a query by passing a column name to the value argument. This will tell me the approximate age of each employee:

 SELECT FirstName, LastName, DATEDIFF(year, BirthDate, GETDATE()) FROM Employee  

Figure 6-8 shows the first 25 rows in the results.

Figure 6-8:

This may look right at first glance, but it's not accurate to the day. For example, according to the database, Brian Welker's birth date is on July 8 and he would be celebrating his 38th birthday this year (I'm running the query in April). If I were to use the previous calculation to determine when his age changes, I would be sending Brian a birthday card sometime in January, about six months early.

Unless you find the difference between these dates in a more granular unit and then do the math, the result will only be accurate within a year of the employee's actual birth date. This example factors the number of days in a year (including leap year). Converting to an Int type truncates, rather than rounds, the value:

 SELECT FirstName, LastName  , CONVERT(Int, DATEDIFF(day, BirthDate, GETDATE())/365.25) As Age  FROM Employee 

Compare the results shown in Figure 6-9 with those of the previous example.

Figure 6-9:

Now Brian is 37 and the rest of the employees' ages should be accurate within a day. The BirthDate column in this table stores the employee's birth date as of midnight (00:00:00 AM). This is the first second of a date. The GETDATE() function returns the current date and time. This means that I'm comparing a two dates with a difference of about eight hours (it's about 8:00 AM as I write this). If you want this calculation to be even more accurate, you need to convert the result of the GETDATE() function to a DateTime value at midnight of the current date.

The DATEPART() and DATENAME() Functions

These functions return the date part, or unit, for a DateTime or ShortDateTime value. The DATEPART() function returns an integer value and the DATENAME() function returns a string containing the descriptive name, if applicable. For example, passing the date 4-29-1988 to the DATEPART() function and requesting the month returns the number 4:

 SELECT DATEPART(month, '4-29-1988') 

Whereas, with the same parameters, the DATENAME() function returns April:

 SELECT DATENAME(month, '4-29-1988') 

Both of these functions accept values from the same list of date part argument constants as the DATEADD() function.

The GETDATE() and GETUTCDATE() Functions

Both of these functions return the current date and time as a DateTime type. The GETUTCDATE() function uses the time zone setting on the server to determine the equivalent Universal Time Coordinate time. This is the same as Greenwich Mean Time or what pilots call "Zulu Time." Both functions are accurate to 3.33 milliseconds:

 SELECT GETDATE() SELECT GETUTCDATE() 

Executing these functions returns the unformatted result shown in Figure 6-10.

Figure 6-10:

Because I'm in the Pacific time zone, there is a seven-hour difference between the current time and UTC. I can verify this using the following DATEDIFF() function call:

 SELECT DATEDIFF(hour, GETDATE(), GETUTCDATE()) 

The DAY(), MONTH(), and YEAR() Functions

These three functions return an integer date part of a DateTime or SmallDateTime type value. They serve a variety of useful purposes including the ability to create your own unique date formats. Suppose I need to create a custom date value as a character string. By converting the output from each of these functions to character types and then concatenating the results, I can arrange them practically any way I want:

 SELECT 'Year: ' + CONVERT(VarChar(4), YEAR(GETDATE()))  + ', Month: ' + CONVERT(VarChar(2), MONTH(GETDATE()))  + ', Day: ' + CONVERT(VarChar(2), DAY(GETDATE())) 

This script produces the following:

Year: 2005, Month: 4, Day: 27

The next section discusses string manipulation functions and uses a similar technique to build a compact custom time stamp.

String Manipulation Functions

String functions are used to parse, replace, and manipulate character values. One of the great challenges when working with quantities or raw character data is to reliably extract meaningful information. A number of string parsing functions are available to identify and parse substrings (a portion of a larger character type value). As humans, we do this all the time. When presented with a document, an invoice, or written text, we intuitively identify and isolate the meaningful pieces of information. To automate this process can be a cumbersome task when dealing with even moderately complex text values. These functions contain practically all of the tools necessary. The challenge is to find the simplest and most elegant method.

The ASCII(), CHAR(), UNICODE(), and NCHAR() Functions

These four functions are similar because they all deal with converting values between a character and the industry standard numeric representation of a character. The American Standard Code for Information Interchange (ASCII) standard character-set includes 128 alpha, numeric, and punctuation characters. This set of values is the foundation of the IBM PC architecture, and although some of it is now somewhat antiquated, much remains and is still central to modern computing. If you use the English language on your computer, every character on your keyboard is represented in the ASCII character-set. This is great for English-speaking (or at least English typing) computer users, but what about everyone else on the planet?

In the evolution of the computer, it didn't take long for the ASCII set to become obsolete. It was soon extended to form the 256-character ANSI character-set which uses a single byte to store every character. Still an American standard (held by the American National Standards Institute), this extended list of characters meets the needs of many other users, supporting mainly European language characters, but is still founded on the original English-language character-set. To support all printable languages, the Unicode standard was devised to support multiple, language-specific character-sets. Each Unicode character requires 2 bytes of storage space, twice the space as ASCII and ANSI characters, but with 2 bytes more than 65,000 unique characters can be represented. SQL Server supports both ASCII and Unicode standards.

The two ASCII-based functions are ASCII() and CHAR().The fundamental principle here is that every character used on the computer is actually represented as a number. To find out what number is used for a character, pass a single-character string to the ASCII() function:

 SELECT ASCII('A') 

This returns 65.

What if I know the number and want to convert it to a character? That's the job of the CHAR() function:

 SELECT CHAR(65) 

This returns the letter A.

To get a complete list of ASCII character values, I can populate a temporary table with the values 0 through 127 and then use the CHAR() function to return the corresponding characters. I'll shorten the script but include the entire result set in multi-column format, to save space:

 -- Create temporary table for numbers: Create Table #ASCIIVals (ASCIIValue SmallInt) -- Insert numbers 0 - 127 into table: Insert Into #ASCIIVals (ASCIIValue) Select 0 Insert Into #ASCIIVals (ASCIIValue) Select 1 Insert Into #ASCIIVals (ASCIIValue) Select 2 Insert Into #ASCIIVals (ASCIIValue) Select 3 Insert Into #ASCIIVals (ASCIIValue) Select 4 ... Insert Into #ASCIIVals (ASCIIValue) Select 123 Insert Into #ASCIIVals (ASCIIValue) Select 124 Insert Into #ASCIIVals (ASCIIValue) Select 125 Insert Into #ASCIIVals (ASCIIValue) Select 126 Insert Into #ASCIIVals (ASCIIValue) Select 127 -- Return all integer values and corresponding ASCII characters: SELECT ASCIIValue, CHAR(ASCIIValue) As Character FROM #ASCIIVals 

Here are the results reformatted in a multi-column grid. Note that non-printable control characters show as small squares in the results grid. Depending on a number of factors, such as fonts or languages installed, these may be displayed a little differently.

ASCIIValue	Character	ASCIIValue	Character	ASCIIValue	Character	ASCIIValue	Character
0		32		64	@	96	`
1		33	!	65	A	97	a
2		34	“	66	B	98	b
3		35	#	67	C	99	c
4		36	$	68	D	100	d
5		37	%	69	E	101	e
6		38	&	70	F	102	f
7		39	‘	71	G	103	g
8		40	(	72	H	104	h
9		41	)	73	I	105	i
10		42	*	74	J	106	j
11		43	+	75	K	107	k
12		44	,	76	L	108	l
13		45	-	77	M	109	m
14		46	.	78	N	110	n
15		47	/	79	O	111	o
16		48	0	80	P	112	p
17		49	1	81	Q	113	q
18		50	2	82	R	114	r
19		51	3	83	S	115	s
20		52	4	84	T	116	t
21		53	5	85	U	117	u
22		54	6	86	V	118	v
23		55	7	87	W	119	w
24		56	8	88	X	120	x
25		57	9	89	Y	121	y
26		58	:	90	Z	122	z
27		59	;	91	[	123	{
28		60	<	92	\	124	|
29		61	=	93	]	125	}
30	-	62	>	94	^	126	~
31		63	?	95	_	127

The UNICODE() function is the Unicode equivalent of the ASCII() function, and the NCHAR() function does the same thing as the CHAR() function only with Unicode characters. SQL Server's nChar and nVarChar types will store any Unicode character and will work with this function. For extremely large values, the nText type and the new nChar(max) and nVarChar(max) types in SQL Server 2005 also support Unicode characters.

To return extended characters, I'll execute the NCHAR() function with sample character codes:

 SELECT NCHAR(220) 

This returns the German U umlaut, .

 SELECT NCHAR(233) 

This returns an accented lowercase e, .

 SELECT NCHAR(241) 

This returns a Spanish "enya," or n with a tilde, .

The CHARINDEX() and PATINDEX() Functions

CHARINDEX() is the original SQL function used to find the first occurrence of a substring within another string. As the name suggests, it simply returns an integer that represents the index of the first character of the substring within the entire string. The following script looks for an occurrence of the string 'sh' within the string 'Washington':

 SELECT CHARINDEX('sh', 'Washington') 

This returns 3 to indicate that the 's' is the third character in the string 'Washington'. Using two characters for the substring wasn't particularly useful in this example but could be if the string contained more than one letter s.

The PATINDEX() function is the CHARINDEX() function on steroids. It will perform the same task in a slightly different way, but has the added benefit of supporting wildcard characters (such as those you would use with the Like operator). As its name suggests, it will return the index of a pattern of characters. This function also works with large character types such as nText, nChar(max), and nVarChar(max). Note that if PATINDEX() is used with these large data types, it returns a BigInt type rather than an Int type. Here's an example:

 SELECT PATINDEX('%M_rs%', 'The stars near Mars are far from ours') 

Note that both percent characters are required if you want to find a string with zero or more characters before and after the string being compared. The underscore indicates that the character in this position is not matched. The string could contain any character at this position.

Compare this to the CHARINDEX() function used with the same set of strings:

 SELECT CHARINDEX('Mars', 'The stars near Mars are far from ours') 

Both of these functions return the index value 16. Remember how these functions work. I'll combine this with the SUBSTRING() function in the following section to demonstrate how to parse strings using delimiting characters.

The LEN() Function

The LEN() function returns the length of a string as an integer. This is a simple but useful function that is often used alongside other functions to apply business rules. The following example tests the Month and Day date parts integers, converted to character types, for their length. If just one character is returned, it pads the character with a zero and then assembles an eight-character date string in US format (MMDDYYYY):

 DECLARE @MonthChar VarChar(2), @DayChar VarChar(2), @DateOut Char(8) SET @MonthChar = CAST(MONTH(GETDATE()) AS VarChar(2)) SET @DayChar = CAST(DAY(GETDATE()) AS VarChar(2)) -- Make sure month and day are two char long: IF LEN(@MonthChar) = 1 SET @MonthChar = ‘0’ +  IF LEN(@DayChar) = 1 SET @DayChar = ‘0’ + @DayChar -- Build date string: SET @DateOut = @MonthChar + @DayChar + CAST(YEAR(GETDATE()) AS Char(4)) SELECT @DateOut 

The return value from this script will always be an eight-character value representing the date:

05012005

The LEFT() and RIGHT() Functions

The LEFT() and RIGHT() functions are similar in that they both return a substring of a specified size. The difference between the two is what part of the character string is returned. The LEFT() function returns characters from the left-most part of the string, counting characters to the right. The RIGHT() function does exactly the opposite. It starts at the right-most character and counts to the left, returning the specified number of characters. Take a look at an example that uses the string 'George Washington' to return substrings using these functions.

If I ask to return a five-character substring using the LEFT() function, the function locates the left-most character, counts five characters to the right, and returns the substring shown in Figure 6-11.

Figure 6-11:

 DECLARE @FullName VarChar(25) SET @FullName = 'George Washington'  SELECT LEFT(@FullName, 5) Geor

If I ask to return a five-character substring using the RIGHT() function, the function locates the rightmost character, counts five characters to the left, and returns this substring shown in Figure 6-12.

Figure 6-12:

 DECLARE @FullName VarChar(25) SET @ FullName = 'George Washington'  SELECT RIGHT(@FullName, 5) ngton

Neither of these functions is particularly useful for consistently returning a meaningful part of this string. What if I wanted to return the first name or last name portions of the full name? This takes just a little more work. The LEFT() function may be the right method to use for extracting the first name if I can determine the position of the space in every name I might encounter. In this case, I can use the CHARINDEX() or PATINDEX() functions to locate the space and then use the LEFT() function to return only these characters. The first example here takes a procedural approach, breaking this process into steps:

 DECLARE @FullName VarChar(25), @SpaceIndex TinyInt SET @FullName = ‘George  -- Get index of the delimiting space: SET @SpaceIndex = CHARINDEX(‘ ‘, @FullName) -- Return all characters to the left of the space: SELECT LEFT(@FullName, @SpaceIndex - 1) 

I don't want to include the space so it's necessary to subtract one from the @SpaceIndex value to include only the first name.

The SUBSTRING() Function

The SUBSTRING() function starts at a position and counts characters to the right, returning a substring of a specified length. Unlike the LEFT() function, you can tell it at what index position to begin counting. This allows you to extract a substring from anywhere within a character string. This function requires three arguments: the string to parse, the starting index, and the length of the substring to return. If you want to return all text to the end of the input string, you can use a length index larger than necessary. The SUBSTRING() function will return characters up to the last position of the string and will not pad the string with spaces.

The SUBSTRING() function can easily replace the LEFT() function by designating the left-most character of the string (1) as the starting index.

Continuing with the earlier example, I can set the starting position and length, returning a value from the middle of the name string. In this case, I'll start at position 4 and return a 6-character substring, as shown in Figure 6-13.

Figure 6-13:

 DECLARE @FullName VarChar(25) SET @FullName = 'George Washington'  SELECT SUBSTRING(@FullName, 4, 6)  rge Wa

Now, I'll put it all together and parse the first and last names from the full name in a way that will work for any full name string formatted as FirstName + space + LastName. Using the same logic as before, I'm going to nest the function calls to reduce the number of lines of script and get rid of the @SpaceIndex variable. Instead of the LEFT() function, I'll use SUBSTRING(). I've added a comment below the line that does all the work. Spaces are added to make room for the comment text:

 DECLARE @FullName VarChar(25) SET @FullName = ‘George Washington’ -- Return first name: SELECT SUBSTRING(@FullName, 1,      CHARINDEX(‘ ‘, @FullName) - 1) --               ^String    ^Start  ^Returns space index      ^Don’t include space 

Similar logic is used to extract the last name. I just have to change the start index argument to the position following the space. The space is at position seven and the last name begins at position eight. This means that the start index will always be one plus the CHARINDEX() result:

 DECLARE @FullName VarChar(25) SET @FullName = 'George Washington'  SELECT SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName)) 

The values passed into the SUBSTRING() function are the position of the space plus one as the start index. This will be the first letter of the last name. Because I won't always know the length of the name, I passed in the LEN() function for the length of the substring. The SUBSTRING() function will reach the end of the string when it reaches this position and simply include all characters after the space to the end of the string.

To set up an example, I'll create and populate a temporary table:

 CREATE TABLE #MyNames (FullName VarChar(50)) GO INSERT INTO #MyNames (FullName) SELECT ‘Fred  INSERT INTO #MyNames (FullName) SELECT ‘Wilma  INSERT INTO #MyNames (FullName) SELECT ‘Barney  INSERT INTO #MyNames (FullName) SELECT ‘Betty  INSERT INTO #MyNames (FullName) SELECT ‘George  INSERT INTO #MyNames (FullName) SELECT ‘Jane  

Now I'll execute a query using the function calls to parse the first name and last name values as one-line expressions. Note that references to the @FullName variable are replaced with the FullName column in the table:

 SELECT SUBSTRING(FullName, 1, CHARINDEX(‘ ‘, FullName) - 1) AS FirstName , SUBSTRING(FullName, CHARINDEX(‘ ‘, FullName) + 1, LEN(FullName)) AS LastName FROM #MyNames 

The results shown in Figure 6-14 display two distinct columns as if the first and last names were stored separately.

Figure 6-14:

The LOWER() and UPPER() Functions

These functions are pretty easy to figure out. Each simply converts a character string to all lowercase or all uppercase characters. This is most useful when comparing user input or stored strings for comparison. String comparisons are typically case-insensitive, depending on settings chosen during SQL Server setup. Used along with other string manipulation functions, strings can be converted to use proper case for data storage and presentation. This example accounts for mixed-case last names, assuming the name contains a single space before the second capitalized substring. You could argue that some of these names normally wouldn't contain spaces, and I agree. This demonstration could easily be extended to include provisions for other mixed-case names (names beginning with Mc, hyphenated names, and so on).

 DECLARE @LastName VarChar(25), @SpaceIndex TinyInt SET @LastName = ‘mc donald’ -- Find space in name: SET @SpaceIndex = CHARINDEX(‘ ‘, @LastName) IF @SpaceIndex > 0                       -- Space: Capitalize first & substring SELECT UPPER(LEFT(@LastName, 1))  + LOWER(SUBSTRING(@LastName, 2, @SpaceIndex - 1))  + UPPER(SUBSTRING(@LastName, @SpaceIndex + 1, 1))  + LOWER(SUBSTRING(@LastName, @SpaceIndex + 2, LEN(@LastName))) ELSE                                     -- No space: Cap only first char. SELECT UPPER(LEFT(@LastName, 1)) + LOWER(SUBSTRING(@LastName, 2, LEN(@LastName))) 

This script returns Mc Donald. I can also extend the example to deal with last names containing an apostrophe. The business rules in this case expect no space. If an apostrophe is found, the following character is to be capitalized. Note that to test an apostrophe in script, it must be entered twice ('') to indicate that this is a literal, rather than an encapsulating single quote. Last name values are stored with only an apostrophe.

 DECLARE @LastName VarChar(25), @SpaceIndex TinyInt, @AposIndex TinyInt SET @LastName =  -- Find space in name: SET @SpaceIndex = CHARINDEX(‘ ‘, @LastName) -- Find literal ‘ in name: SET @AposIndex =  IF @SpaceIndex > 0                       -- Space: Capitalize first & substring SELECT UPPER(LEFT(@LastName, 1))  + LOWER(SUBSTRING(@LastName, 2, @SpaceIndex - 1))  + UPPER(SUBSTRING(@LastName, @SpaceIndex + 1, 1))  + LOWER(SUBSTRING(@LastName, @SpaceIndex + 2, LEN(@LastName)) ELSE IF @AposIndex > 0                   -- Apostrophe: Cap first & substring SELECT UPPER(LEFT(@LastName, 1))  + LOWER(SUBSTRING(@LastName, 2, @AposIndex - 1))  + UPPER(SUBSTRING(@LastName, @AposIndex + 1, 1))  + LOWER(SUBSTRING(@LastName, @AposIndex + 2, LEN(LastName))) ELSE                                     -- No space: Cap only first char. SELECT UPPER(LEFT(@LastName, 1)) + LOWER(SUBSTRING(@LastName, 2, LEN(LastName))) 

This script returns O'Malley. For this to be of use, I'll wrap it up into a user-defined function:

 CREATE FUNCTION dbo.fn_FixLastName ( @LastName VarChar(25) ) RETURNS VarChar(25) AS BEGIN DECLARE   @SpaceIndex TinyInt , @AposIndex TinyInt , @ReturnName VarChar(25) -- Find space in name: SET @SpaceIndex = CHARINDEX(‘ ‘, @LastName) -- Find literal ‘ in name: SET @AposIndex =  IF @SpaceIndex > 0                       -- Space: Capitalize first & substring SET @ReturnName = UPPER(LEFT(@LastName, 1))   + LOWER(SUBSTRING(@LastName, 2, @SpaceIndex - 1))  + UPPER(SUBSTRING(@LastName, @SpaceIndex + 1, 1))  + LOWER(SUBSTRING(@LastName, @SpaceIndex + 2, LEN(LastName))) ELSE IF @AposIndex > 0                   -- Apostrophe: Cap first & substring SET @ReturnName =  UPPER(LEFT(@LastName, 1))  + LOWER(SUBSTRING(@LastName, 2, @AposIndex - 1))  + UPPER(SUBSTRING(@LastName, @AposIndex + 1, 1))  + LOWER(SUBSTRING(@LastName, @AposIndex + 2, LEN(LastName))) ELSE                                     -- No space: Cap only first char.   SET @ReturnName =  UPPER(LEFT(@LastName, 1))   + LOWER(SUBSTRING(@LastName, 2, LEN(LastName))) RETURN @ReturnName END 

To test my function, I'll populate a temporary table with sample values so that I can query the names from this table:

 CREATE TABLE #MyIrishFriends (FirstName VarChar(25), LastName VarChar(25) ) INSERT INTO #MyIrishFriends (FirstName, LastName) SELECT  INSERT INTO #MyIrishFriends (FirstName, LastName) SELECT  INSERT INTO #MyIrishFriends (FirstName, LastName) SELECT  INSERT INTO #MyIrishFriends (FirstName, LastName) SELECT  INSERT INTO #MyIrishFriends (FirstName, LastName) SELECT  

The results as they are stored are shown in Figure 6-15.

Figure 6-15:

 SELECT FirstName, LastName  FROM #MyIrishFriends 

Using the custom function returns the results shown in Figure 6-16.

Figure 6-16:

 SELECT FirstName, dbo.fn_FixLastName(LastName) AS LastName FROM #MyIrishFriends 

The LTRIM() and RTRIM() Functions

These two functions simply return a string with white space (spaces) trimmed from either the left or right side of significant characters:

 DECLARE @Value1 Char(10), @Value2 Char(10) SET @Value1 = 'One' SET @Value2 = 'Two' SELECT @Value1 + @Value2 SELECT CONVERT(VarChar(5), LEN(@Value1 + @Value2)) + ' characters long.'  SELECT RTRIM(@Value1) + RTRIM(@Value2)  SELECT CONVERT(VarChar(5), LEN(RTRIM(@Value1) + RTRIM(@Value2)))  + ' characters long trimmed.' 

The abbreviated results in text form follow:

--------------------  One       Two        ----------------------  13 characters long.     --------------------  OneTwo                             ------------------------------  6 characters long trimmed.

The REPLACE() Function

The REPLACE() function can be used to replace all occurrences of one character or substring with another character or substring. This can be used as a global search and replace utility.

 DECLARE @Phrase VarChar(1000) SET @Phrase = ‘I aint gunna use poor grammar when commenting script and I aint gunna complain about it.’ SELECT REPLACE(@Phrase, ‘aint’,  

As you can see, this was quite effective:

I isn't gunna use poor grammar when commenting script and I isn't gunna complain  about it.

The REPLICATE() and SPACE() Functions

This is a very useful function when you need to fill a value with repeating characters. I'll use the same temporary table I created for the list of names in the SUBSTRING() example to pad each name value to 20 characters. I subtract the length of each value to pass the right value to the REPLICATE() function:

 SELECT FullName + REPLICATE('*', 20 - LEN(FullName))  FROM #MyNames 

The result is a list of names padded with asterisk characters, each 20 characters in length:

Fred Flintstone***** Wilma Flintstone**** Barney Rubble******* Betty Rubble******** George Jetson******* Jane Jetson*********

The SPACE() function does the same thing, only with spaces. It simply returns a string of space characters of a defined length.

The REVERSE() Function

This function reverses the characters in a string. This might be useful if you need to work with single-character values in a concatenated list.

 SELECT REVERSE('The stars near Mars are far from ours.') .sruo morf raf era sraM raen srats ehT

I'm sure there's a practical application for this.

The STUFF() Function

This function allows you to replace a portion of a string with another string. It essentially will stuff one string into another string at a given position and for a specified length. This can be useful for string replacements where the source and target values aren't the same length. For example, I need to replace the price in this string, changing it from 99.95 to 109.95:

Please submit your payment for 99.95 immediately.

The price value begins at position 32 and is five characters in length. It really doesn't matter how long the substring is that I want to stuff into this position. I simply need to know how many characters need to be removed.

 SELECT STUFF('Please submit your payment for 99.95 immediately.', 32, 5, '109.95') 

The resulting string follows:

Please submit your payment for 109.95 immediately.

The QUOTENAME() Function

This function is used with SQL Server object names so they can be passed into an expression. It simply returns a string with square brackets around the input value. If the value contains reserved delimiting or encapsulating characters (such as quotation marks or brackets), modifications are made to the string so SQL Server perceives these characters as literals.

Image/Text Functions

The Text, nText, and Image data types define columns that can store up to 2 gigabytes of ANSI text, Unicode text, or binary data, respectively. SQL Server 2005 still has support for these data types, but Microsoft recommends the use of the new VarChar(max) and VarBinary(max) types in their place. These enhanced data types have the same capabilities and storage characteristics as their older counterparts, but they also support all of the string functions like standard character types. Older types may eventually be phased out of future SQL Server versions.

Two functions have specialized functionality specific to the Text, nText, and Image data types. Additionally, the PATINDEX() string function can be used to find a string of text within these columns and return an integer representing the character position of the first occurrence of the string.

Mathematical Functions

The functions listed in the following table are used to perform a variety of common and specialized mathematical operations and are useful in performing algebraic, trigonometric, statistical, approximating, and financial operations.

Function	Description
ABS()	Returns the absolute value for a numeric value.
ACOS()	Computes the arccosine (an angle) in radians.
ASIN()	Computes the arcsine (an angle) in radians.
ATAN()	Computes the arctangent (an angle) in radians.
ATN2()	Computes the arctangent of two values in radians.
CEILING()	Returns the smallest integer value that is greater than or equal to a number.
COS()	Computes the cosine of an angle in radians.
COT()	Computes the cotangent of an angle in radians.
DEGREES()	Converts an angle from radians to degrees.
EXP()	Returns the natural logarithm raised to a specified exponent.
FLOOR()	Returns the largest integer value that is less than or equal to a number.
LOG()	Calculates the natural logarithm of a number using base-2 (binary) numbering.
LOG10()	Calculates the natural logarithm of a number using base-10 numbering.
PI()	Returns the value for PI() as a float type.
POWER()	Raises a value to a specified exponent.
RADIANS()	Converts an angle from degrees to radians.
RAND()	Returns a fractional number based on a randomizing algorithm; accepts an optional seed value.
ROUND()	Rounds a fractional value to a specified precision.
SIGN()	Returns -1 or 1 depending on whether a single argument value is negative or positive.
SIN()	Computes the sine of an angle in radians.
SQRT()	Returns the square root of a value.
SQUARE()	Returns the square (n2) of a value.
TAN()	Computes the tangent of an angle in radians.

Metadata Functions

These are utility functions that return information about the SQL Server configuration details and details about the server and database settings. This includes a range of general and special-purpose property-related functions that will return the state of various object properties. These functions wrap queries from the system tables in the Master database and a user database. It's recommended that you use these and other system functions rather than creating queries against the system tables yourself, in case schema changes are made in future versions of SQL Server. Some of the information listed in the following table can also be obtained using the INFORMATION_SCHEMA views.

Function	Description
COL_LENGTH()	Returns the length of a column from the column name.
COL_NAME()	Returns the name of a column from the object ID.
COLUMNPROPERTY()	Returns a flag to indicate the state of a column property. Properties include AllowsNull, IsComputed, IsCursorType, IsDeterministic, IsFulltextIndexed, IsIdentity, IsIdNotForRepl, IsIndexable, IsOutParam, IsPrecise, IsRowGuidCol, Precision, Scale, and UsesAnsiTrim.
DATABASEPROPERTY()	This function is maintained for backward compatibility with older SQL Server versions. Returns a flag to indicate the state of a database property. Properties include IsAnsiNullDefault, IsAnsiNullsEnabled, IsAnsiWarningsEnabled, IsAutoClose, IsAutoCreateStatistics, IsAutoShrink, IsAutoUpdateStatistics, IsBulkCopy, IsCloseCursorsOnCommitEnabled, IsDboOnly, IsDetached, IsEmergencyMode, IsFulltextEnabled, IsInLoad, IsInRecovery, IsInStandBy, IsLocalCursorsDefault, IsNotRecovered, IsNullConcat, IsOffline, IsQuotedIdentifiersEnabled, IsReadOnly, IsRecursiveTriggersEnabled, IsShutDown, IsSingleUser, IsSuspect, IsTruncLog, and Version.
DATABASEPROPERTYEX()	Returns a flag to indicate the state of a database property. Properties include Collation, IsAnsiNullDefault, IsAnsiNullsEnabled, IsAnsiPaddingEnabled, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoClose, IsAutoCreateStatistics, IsAutoShrink, IsAutoUpdateStatistics, IsCloseCursorsOnCommitEnabled, IsFulltextEnabled, IsInStandBy, IsLocalCursorsDefault, IsMergePublished, IsNullConcat, IsNumericRoundAbortEnabled, IsQuotedIdentifiersEnabled, IsRecursiveTriggersEnabled, IsSubscribed, IsTornPageDetectionEnabled, Recovery, SQLSortOrder, Status, Updateability, UserAccess, and Version.
DB_ID()	Returns the database ID from the database name.
DB_NAME()	Returns the database name from the database ID.
FILE_ID()	Returns the file ID from the file name.
FILE_NAME()	Returns the file name from the file ID.
fn_listextendedproperty()	Returns a table object populated with extended property names and their settings.
FULLTEXTCATALOGPROPERTY()	Returns a flag to indicate the state of a full-text catalog property. Properties include PopulateStatus, ItemCount, IndexSize, UniqueKeyCount, LogSize, and PopulateCompletionAge.
FULLTEXTSERVICEPROPERTY()	Returns a flag to indicate the state of a full-text service property. Properties include ResourceUsage, ConnectTimeout, IsFulltextInstalled, and DataTimeout.
INDEX_COL()	Returns the name of a column contained in a specified index, by table, index, and column ID.
INDEXKEY_PROPERTY()	Returns a flag to indicate the state of an index key property. Properties are ColumnId and IsDescending.
INDEXPROPERTY()	Returns a flag indicating the state of an index property. Properties include IndexDepth, IndexFillFactor, IndexID, IsAutoStatistics, IsClustered, IsFulltextKey, IsHypothetical, IsPadIndex, IsPageLockDisallowed, IsRowLockDisallowed, IsStatistics, and IsUnique.
OBJECT_ID()	Returns an object ID from the object name.
OBJECT_NAME()	Returns an object name from the object ID.
OBJECTPROPERTY()	This function allows you to get property information from several different types of objects. It is advisable to use a function designed to query specific object types if possible. Returns a flag indicating the state of an object property. Properties include CnstIsClustKey, CnstIsColumn, CnstIsDeleteCascade, CnstIsDisabled, CnstIsNonclustKey, CnstIsNotRepl, CnstIsNotTrusted, CnstIsUpdateCascade, ExecIsAfterTrigger, ExecIsAnsiNullsOn, ExecIsDeleteTrigger, ExecIsFirstDeleteTrigger, ExecIsFirstInsertTrigger, ExecIsFirstUpdateTrigger, ExecIsInsertTrigger, ExecIsInsteadOfTrigger, ExecIsLastDeleteTrigger, ExecIsLastInsertTrigger, ExecIsLastUpdateTrigger, ExecIsQuotedIdentOn, ExecIsStartup, ExecIsTriggerDisabled, ExecIsUpdateTrigger, HasAfterTrigger, HasInsertTrigger, HasInsteadOfTrigger, HasUpdateTrigger, IsAnsiNullsOn, IsCheckCnst, IsConstraint, IsDefault, IsDefaultCnst, IsDeterministic, IsExecuted, IsExtendedProc, IsForeignKey, IsIndexable, IsIndexed, IsInlineFunction, IsMSShipped, IsPrimaryKey, IsProcedure, IsQuotedIdentOn, IsReplProc, IsRule, IsScalarFunction, IsSchemaBound, IsSystemTable, IsTable, IsTableFunction, IsTrigger, IsUniqueCnst, IsUserTable, IsView, OwnerId, TableDeleteTrigger, TableDeleteTriggerCount, TableFullTextBackgroundUpdateIndexOn, TableFulltextCatalogId, TableFullTextChangeTrackingOn, TableFulltextKeyColumn, TableFullTextPopulateStatus, TableHasActiveFulltextIndex, TableHasCheckCnst, TableHasClustIndex, TableHasDefaultCnst, TableHasDeleteTrigger, TableHasForeignKey, TableHasForeignRef, TableHasIdentity, TableHasIndex, TableHasInsertTrigger, TableHasNonclustIndex, TableHasPrimaryKey, TableHasRowGuidCol, TableHasTextImage, TableHasTimestamp, TableHasUniqueCnst, TableHasUpdateTrigger, TableInsertTrigger, TableInsertTriggerCount, TableIsFake, TableIsPinned, TableTextInRowLimit, TableUpdateTrigger, and TableUpdateTriggerCount.

Ranking Functions

These are new functions in SQL Server 2005 used to enumerate sorted and top-valued result sets using a specified order, independent from the order of the result set.

The ROW_NUMBER() Function

The ROW_NUMBER() function returns an integer with a running incremental value based on an ORDER BY clause passed to this function. If the ROW_NUMBER's ORDER BY matches the order of the result set, the values will be incremental and in ascending order. If the ROW_NUMBER's ORDER BY clause is different than the order of the results, these values will not be listed in order but will represent the order of the ROW_NUMBER function's ORDER BY clause.

 SELECT  ProductCategoryID , Name , ROW_NUMBER() Over (ORDER BY Name) As RowNum FROM ProductCategory ORDER BY Name 

With the ORDER BY clause on the ROW_NUMBER() call matching the order of the query, these values are listed in order (see Figure 6-17).

Figure 6-17:

However, when using a different ORDER BY clause in the function call, these values are not ordered.

 SELECT  ProductCategoryID , Name , ROW_NUMBER() Over (ORDER BY Name) As RowNum FROM ProductCategory ORDER BY ProductCategoryID 

This provides an effective means to tell how the result would have been sorted using the other ORDER BY clause, as shown in Figure 6-18.

Figure 6-18:

The RANK() and DENSE_RANK() Functions

Both of these functions are similar to the ROW_NUMBER() function in that they return a value based on an ORDER BY clause, but these values may not always be unique. Ranking values are repeated for duplicate results from the provided ORDER BY clause, and uniqueness is only based on unique values in the ORDER BY list. Each of these functions takes a different approach to handling these duplicate values. The RANK() function preserves the ordinal position of the row in the list. For each duplicate value, it skips the subsequent value so that the next non-duplicate value remains in its rightful position.

SELECT        ProductCategoryID     , Name     , RANK() Over (ORDER BY Name) As Rank FROM ProductCategory ORDER BY Name

Note in the result set shown in Figure 6-19 that the values are repeated for duplicated name values and the skipped values following each tie. For example, both rows for employees named Andrew are ranked number 8, and the following row, Andy, is ranked number 10.

Figure 6-19:

The DENSE_RANK() function works exactly the same way, but it doesn't skip numbers after each tie. This way, no values are skipped, but the ordinal ranking position is lost whenever there are ties.

 SELECT  ProductCategoryID , Name , DENSE_RANK() Over (ORDER BY Name) As Rank FROM ProductCategory ORDER BY Name 

The result shown in Figure 6-20 repeats ranked values but doesn't skip any numbers in this column.

Figure 6-20:

The NTILE(n) Function

This function also ranks results, returning an integer ranking value. However, rather than enumerating the results into uniquely ranked order, it divides the result into a finite number of ranked groups. For example, if a table has 10,000 rows and the NTILE() function is called with an argument value of 1000, as NTILE(1000), the result would be divided into 1000 groups of 10, with each group being assigned the same ranking value. The NTILE() function also supports the OVER (ORDER BY. . . ) syntax like the other ranking functions discussed in this section.

Security Functions

The security-related functions return role membership and privilege information for SQL Server users. This category also includes a set of functions to manage events and traces, as described in the following table.

Function	Description
fn_trace_geteventinfo()	Returns a table type populated with event information for a specified trace ID.
fn_trace_getfilterinfo()	Returns a table type populated with information about filters applied for a specified trace ID.
fn_trace_getinfo()	Returns a table type populated with trace information for a specified trace ID.
fn_trace_gettable()	Returns a table type populated with file information for a specified trace ID.
HAS_DBACCESS()	Returns a flag indicating whether the current user has access to a specified database.
IS_MEMBER()	Returns a flag indicating whether the current user is a member of a Windows group or SQL Server role.
IS_SRVROLEMEMBER()	Returns a flag indicating whether the current user is a member of a database server role.
SUSER_SID()	Returns either the security ID for a specified user's login name or (if the parameter is omitted) returns the security ID of the current user. Returns either the user ID for a specified username or (if the parameter is omitted) returns the user ID of the current user.
SUSER_SNAME()	Returns the Login name for a specified security ID. If no security ID is provided it returns the login for the current connection.
USER_ID()	Returns either the user ID for a specified username or (if the parameter is omitted) returns the user ID of the current user.
USER_NAME()	Returns a username for a specified user ID.

System Functions and Variables

This section discusses utility functions used to perform a variety of tasks. These include value comparisons and value type testing. This category is also a catch-all for other functionality.

Function	Description
APP_NAME()	Returns the name of the application associated with the current connection.
COALESCE()	Returns the first non-null value from a comma-delimited list of expressions.
COLLATIONPROPERTY()	Returns the value of a specific property for a specified collation. Properties include CodePage, LCID, and ComparisonStyle.
CURRENT_TIMESTAMP()	Returns the current date and time and is synonymous with the GETDATE() function. It exists for ANSI-SQL compliance.
CURRENT_USER()	Returns the name of the current user and is synonymous with the USER_NAME() function.
DATALENGTH()	Returns the numbers of bytes used to store or handle a value. For ANSI string types, this will return the same value as the LEN() function but for other data types the value may be different.
fn_helpcollations()	Returns a table type populated with a list of collations supported by the current version of SQL Server.
fn_servershareddrives()	Returns a table type populated with a list of drives shared by the server.
fn_virtualfilestats()	Returns a table type populated with I/O statistics for database files, including log files.
FORMATMESSAGE()	Returns an error message from the sysmessages table for a specified message number and comma-delimited list of parameters.
GETANSINULL()	Returns the nullability setting for the database, according to the ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF database settings.
HOST_ID()	Returns the workstation ID for the current session.
HOST_NAME()	Returns the workstation name for the current session.
IDENT_CURRENT()	Returns the last identity value generated for a specified table regardless of the session and scope.
IDENT_INCR()	Returns the increment value specified in the creation of the last identity column.
IDENT_SEED()	Returns the seed value specified in the creation of the last identity column.
IDENTITY()	Used in a SELECT. . . INTO statement to insert an automatically generated identity value into a column.
ISDATE()	Returns a flag to indicate whether a specified value is or is not capable of being converted to a date value.
ISNULL()	Determines whether a specified value is null and then returns a provided replacement value.
ISNUMERIC()	Returns a flag to indicate whether a specified value is or is not capable of being converted to a numeric value.
NEWID()	Returns a newly generated UniqueIdentifier type value. This is a 128-bit integer, globally unique value, usually expressed as an alpha-numeric hexadecimal representation (for example, ). This type is often used for primary key values in replicated and semi-connected systems.
NULLIF()	Returns a NULL value when two specified arguments have equivalent values.
PARSENAME()	Returns a specific part of a four-part object name.
PERMISSIONS()	Returns an integer whose value is a bit-wise map indicating the permission or combination of permissions for the current user on a specified database object.
ROWCOUNT_BIG()	Like the @@ROWCOUNT variable, returns the number of rows either returned or modified by the last statement. Returns a BigInt type.
SCOPE_IDENTITY()	Like the @@IDENTITY variable, this function returns the last Identity value generated but is limited to the current session and scope (stored procedure, batch, or module).
SERVERPROPERTY()	Returns a flag indicating the state of a server property. Properties include Collation, Edition, Engine Edition, InstanceName, IsClustered, IsFullTextInstalled, IsIntegratedSecurityOnly, IsSingleUser, IsSyncWithBackup, LicenseType, MachineName, NumLicenses, ProcessID, ProductLevel, ProductVersion, and ServerName.
SESSION_USER	Returns the current username. Function is called without parentheses.
SESSIONPROPERTY()	Returns a flag indicating the state of a session property. Properties include ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT, and QUOTED_IDENTIFIER.
STATS_DATE()	Returns a date that statistics for a specified index were last updated.
SYSTEM_USER	Returns the current username. Function is called without parentheses.
USER_NAME()	Returns the username for a specified User ID. If no ID number is provided it returns the current database user.

Some examples related to a few of the functions listed in the preceding table follow.

The COALESCE() Function

The COALESCE() function can be very useful, saving quite a lot of IF or CASE decision logic. The following example populates a table of products, showing up to three prices each:

 CREATE TABLE #ProductPrices (ProductName VarChar(25), SuperSalePrice Money NULL, SalePrice Money NULL, ListPrice Money NULL) GO INSERT INTO #ProductPrices VALUES(‘Standard  INSERT INTO #ProductPrices VALUES(‘Economy Widget’, INSERT INTO #ProductPrices VALUES(‘Deluxe Widget’, INSERT INTO #ProductPrices VALUES(‘Super Deluxe  INSERT INTO #ProductPrices VALUES(‘Executive  GO 

All products have a list price, some have a sale price, and others may have a super sale price. The current price of a product is going to be the lowest existing price, or the first non-null value when reading each of the price columns as they are listed:

 SELECT ProductName, COALESCE(SuperSalePrice, SalePrice, ListPrice) AS CurrentPrice  FROM #ProductPrices 

This method is far more elegant than using multiple lines of branching and decision logic, and the result is equally simple, as illustrated in Figure 6-21.

Figure 6-21:

The DATALENGTH() Function

The DATALENGTH() function returns the number of bytes used to manage a value. This can be used to reveal some interesting differences between data types. It's probably no surprise that when a VarChar type is passed to both the DATALENGTH() and LEN() functions, they return the same value:

 DECLARE @Value VarChar(20) SET @Value = 'abc' SELECT DATALENGTH(@Value) SELECT LEN(@Value) 

These statements both return 3 because the VarChar type uses three single-byte characters to store the three-character value. However, if an nVarChar type is used, it takes twice as many bytes to manage a value of the same length:

 DECLARE @Value nVarChar(20) SET @Value = 'abc' SELECT DATALENGTH(@Value) SELECT LEN(@Value) 

The DATALENGTH() function returns 6 because 2 bytes are used to store each character using a Unicode character set. The LEN() function returns 3 because this function returns the number of characters, not the number of bytes. Here's an interesting test. How many bytes does it take to store an integer variable set to the value 2? How about an integer a variable set to 2 billion? Let's find out:

 DECLARE @Value1 Int, @Value2 Int SET @Value1 = 2 SET @Value2 = 2000000000 SELECT DATALENGTH(@Value1) SELECT LEN(@Value1) SELECT DATALENGTH(@Value2) SELECT LEN(@Value2) 

The DATALENGTH() function returns 4 in both cases because the Int type always uses 4 bytes, regardless of the value. The LEN() function essentially treats the integer value as if it were converted to a character type, returning the number of digits, in this case, 1 and 10, respectively.

The following global system variables all return an Int type. These may be useful in stored procedures and other programming objects to implement custom business logic.

Variable	Description
@@ERROR	The last error number for the current session.
@@IDENTITY	The last identity value generated in the current session.
@@ROWCOUNT	The row count for the last execution in the current session that returned a result set.
@@TRANCOUNT	The number of active transactions in the current session. This would result from multiple, nested BEGIN TRANSACTION statements before executing corresponding COMMIT TRANSACTION or ABORT TRANSACTION statements.

System Statistical Functions and Variables

The following table describes administrative utilities used to discover database system usage and environment information.

Variable	Description
@@CONNECTIONS	The number of open connections.
@@CPU_BUSY	The number of milliseconds that SQL Server has been working since the service was last started.
@@IDLE	The number of milliseconds that SQL Server has been idle since the service was last started.
@@IO_BUSY	The number of milliseconds that SQL Server has been processing I/O since the service was last started.
@@PACK_RECEIVED	The number of network packets that SQL Server has received since the service was last started.
@@PACK_SENT	The number of network packets that SQL Server has sent since the service was last started.
@@PACKET_ERRORS	The number of network packet errors that SQL Server has received since the service was last started.
@@TIMETICKS	The number of microseconds per tick.
@@TOTAL_ERRORS	The number of disk I/O errors that SQL Server has received since the service was last started.
@@TOTAL_READ	The number of physical disk reads since the SQL Server service was last started.
@@TOTAL_WRITE	The number of physical disk writes since the SQL Server service was last started.