T-SQL Functions | Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features

Past versions of SQL Server included numerous constructions and elements that were provided to obtain certain programming results and values, process the various data types, and implement various operations and conditions in the DBMS. These were persistent and were created to obviate the need to repeatedly recode such constructions. These elements, and many new ones, have now been brought together in a unified function collection in SQL Server 2005.

Note

Several administrative functions are included in the SQL Server function arsenal; see Chapter Appendix.

Although they are built into SQL Server, they can be referenced from the outside-in T-SQL statements passed through the APIs and using profilers and query tools, like SQL Server Management Studio and the OSQL utility. Functions in SQL Server 2005 also replace the so-called “global variables” that were preceded by the double at (like @@CONNECTION).

SQL Server functions are true to the definition of “function” in that they can take a number of input values and return scalar values or result sets to the calling process. They can also take a blank value to return a specific predefined or predetermined value. For example, used with the SELECT statement, the function DB_NAME() returns a value that represents the current database you are connected to. And the function GETDATE() returns the current date and time. And NEWID() takes “nothing” as an argument and returns a system-generated GUID.

SQL Server 2005 also lets you create your own user-defined functions. But before we get to the user-defined functions, let’s first investigate the myriad built-in functions, many of which you will want to start using the moment you put this book down.

Determinism of Functions

SQL Server functions can be deterministic or nondeterministic. This means that either they always return the same result any time they are called with a specific set of input values, which means the function is deterministic, or they return different results each time they are called with a specific set of input values, which means they are nondeterministic. This is known as the determinism of the function.

For example, a function like DATEADD is deterministic because it always returns the same result for any given set of argument values for the parameters passed to it. GETDATE, on the other hand, is not deterministic. It is always invoked with the same argument, but the return value, the current date and time, changes with each call of the function.

Function determinism was introduced in SQL Server 2000, and thus nondeterministic functions are constrained by the following usage rules:

You cannot create an index on a computed column if the computed_column_expression references any nondeterministic functions.
You cannot create a clustered index on a view if the view also references nondeterministic functions.

Built-in Function Determinism

SQL Server’s built-in functions are either deterministic or nondeterministic according to how the function is implemented. You have no access to change the determinism. The aggregate and string built-in functions are deterministic; however, CHARINDEX and PATINDEX are not, as are all of the configuration, cursor, metadata, security, and system statistical functions. See BOL for a list of functions that are always deterministic.

String Manipulation Functions

T-SQL provides a rich collection of string manipulation and sting management functions. Many of these functions have equivalent functionality in generic programming languages such as VB, C++, and Delphi.

ASCII(character)

The ASCII(character) function returns the ASCII code value of type int of the leftmost character of the character expression evaluated. For example, the expression

 SELECT ASCII('d')

returns “100.” The expression can be of type char or varchar.

CHAR(integer)

The CHAR(integer) function is used to return the ASCII character of its integer code assignment. For example, the expression

 SELECT CHAR(100)

returns “d,” which is the converse of the ASCII() function. Remember, the ASCII codes run from 0 to 255, and thus a null value is returned if you are outside this range. You will obviously not be able to return a code for noninteger values. For example, CHAR(13) is the carriage return code and has no displayable value. On the other hand, you can use the CHAR() function to insert control characters into character strings. For example, the expression

 SELECT CHAR(100)+CHAR(9)+CHAR(68)

returns the values d and D separated by a tab. The most common control characters used are Tab (CHAR(9)), line feed (CHAR(10)), and carriage return (CHAR(13)).

CHARINDEX(expression1, expression2, startlocation)

This function can be used to determine the starting position of the specified phrase in the character string. It is useful for searching textual data. The syntax for this function is CHARINDEX(expression1, expression2, startlocation). The arguments taken are as follows:

expression1 (expression1) Represents the expression that contains the sequence of characters to be found.
expression2 (expression2) Represents the actual string of characters to be evaluated.
startlocation Represents the starting position in ex2 from which to begin searching for ex1. If the start location is a negative number or zero, then the search begins at the beginning of ex2. In my testing of this function, omitting the start location after a comma delimiter causes an error. If you exclude the delimiter, the function computes.

For example, the expression

 SELECT CHARINDEX('small', 'the very small brown fox', 0)

returns the value 10. Using this function, you can easily search for the starting point of a string in a particular record in your table. For example, the expression

 USE NORTHWIND SELECT CHARINDEX('Connection', CompanyName)   FROM Customers WHERE CustomerID = 'EASTC'

returns the value 9. This type of expression is valuable when you need to get into a value and extract a subexpression. For example, in my record numbers for call center systems I tag an agent ID onto an order number and save the entire record as a record number. If a manager needs to check which agent worked the record, the system can extract the agent ID by first locating the starting point in the record where the agent ID begins and then run the GETCHAR() function on the rest of the record. This saves having to create another table or column that references the record number entity with the agent ID entity.

It should be noted that if either expression1 or expression2 is of a Unicode data type, the nvarchar and nchar types respectively, and the other is not, the other is converted to Unicode. In other words, if ex1 is Unicode and ex2 is not, then ex2 is converted to Unicode.

Also, if either expression1 or expression2 is NULL, the CHARINDEX() function returns NULL if the database compatibility level you have set is 70. If the database compatibility level is 65 or earlier, the CHARINDEX() function returns NULL only when both expression1 and expression2 are NULL.

DATALENGTH(variable expression)

This “function” deserves a place in a list of string manipulation functions. It is similar to LEN() referenced later in this section but returns the declared length of a variable or field. For example, the expression

 USE NORTHWIND  SELECT DATALENGTH(Phone)  FROM CUSTOMERS WHERE CustomerID = 'EASTC'

returns the integer value of “28,” while LEN() returns “14,” the actual length of the string in the variable or field.

DIFFERENCE(expression1, expression2)

This function is used to determine the difference between the SOUNDEX algorithm values of two char or varchar strings represented in expression1 and expression2 (see SOUNDEX() later in this section). The return value is an integer on a scale of 0 to 4. The lowest value of “0” indicates the highest difference between the two strings. The highest value of “4” indicates that the two strings “sound” very similar. For example, the expression

 PRINT DIFFERENCE ('foo', 'bar;')

returns a value of “2,” indicating the two strings do not sound the same but are similar in construction. Upon changing “bar” to “boo,” the difference value increases to 3.

LEFT(string, startindex)

This function returns a character string that starts at a specified number of characters from the left of the varchar variable. In can be used in conjunction with the CHARINDEX() function to return the value of a string at the specified index. For example, the expression

 USE NORTHWIND SELECT LEFT(CompanyName, 5)  FROM CUSTOMERS WHERE CustomerID = 'EASTC'

returns the varchar value “Easte.” If you need to simply evaluate the string, just substitute the column name used in “expression” for a string; for example, LEFT(‘codered,’ 4) returns “code.”

The expression can be of character or binary data, a constant, a variable, or a column. It must also be of a data type that can be implicitly convertible to varchar. Use the CAST function to explicitly convert the string to varchar before you evaluate it.

LEN(string)

This function returns the number of characters, not the number of bytes, of the given string expression, excluding trailing blanks. For example, the expression

 USE NORTHWIND  SELECT LEN(Phone)  FROM CUSTOMERS WHERE CustomerID = 'EASTC'

returns the integer value of “14,” which will help us clean up the telephone number column in the Customer table of the Northwind database (see also DATALENGTH()).

LOWER(string)

This function converts all uppercase characters of character or binary data in the expression argument to lowercase and then returns the new expression. For example, the expression

 USE NORTHWIND SELECT LOWER(CompanyName)  FROM CUSTOMERS WHERE CustomerID = 'EASTC'

returns the value “eastern connection.” The string in expression can be a constant, a variable, or a column as shown in the example. The expression string must also be of a type that can be implicitly converted to varchar. Use CAST to explicitly convert the character. See also UPPER() later in this section.

LTRIM(string)

This function returns a character expression after first removing leading blanks. For example, the expression

 PRINT('                 my bunny lies over the hillside')

 returns the value “                                  my bunny lies over the hillside”, with spaces, but the expression

 PRINT LTRIM ('                  so bring back my bunny to me')

returns the value “so bring back my bunny to me,” sans the spaces. The expression must be an expression of character or binary data. It can be a constant, a variable, or a column, but it must of a data type that is implicitly convertible to varchar. Otherwise, use CAST to convert the expression to varchar.

NCHAR(integer)

This function returns the Unicode character with the given integer code, as defined by the Unicode standard. For example, the statement

 PRINT NCHAR(167)

returns the character “§.” The value must be a positive number in the range 0–65535. If you specify a value outside this range, NULL is returned.

QUOTENAME(string, quote character)

This function returns a Unicode string with the delimiters surrounding the string. For example, the statement

 SELECT QUOTENAME('PHONES','"')

returns the value “PHONES.” And the statement

 PRINT QUOTENAME('PHONES','[')

returns the value [PHONES]. The ‘, “, [, ], {, and } are valid quote characters.

REPLACE(‘expression1’, ‘expression2’, ‘expression3’)

This function finds all occurrences of the second string in the first string and then replaces it with the string in the third expression. For example, the statement

 PRINT REPLACE('fog', 'g', 'o')

results in “fog” being changed to “foo.”

This function can be used with both character and binary data.

REPLICATE(character, integer)

This function repeats a character expression for a specified number of times. It is useful for padding if you replicate a space instead of a character. For example, the statement

 PRINT REPLICATE('0', 2)

returns the value ‘00’. The int expression must be a positive whole number. If it is negative, a null string is returned.

REVERSE(string)

This function returns the reverse of a character expression. For example, the statement

 SELECT REVERSE('evol')

returns the value “love.”

RIGHT(string, integer)

This function returns the part of a character string starting a specified number of characters from the right. For example, the statement

 SELECT RIGHT('evol' , 1)

returns the value “1.”

RTRIM(string expression)

This function is the converse of LTRIM. It snips all trailing blanks from the expression passed in the argument placeholder. For example, the statement

 SELECT RTRIM('LOVE                     ')

returns the value “LOVE."

SOUNDEX(string expression)

This function returns the four-character code of the SOUNDEX algorithm that is used evaluate the similarity of two strings (see the DIFFERENCE() function discussed earlier). For example, the statement

 SELECT SOUNDEX('WASH')

returns the value “W200.”

The SOUNDEX() function converts an alpha string to a four-character code to find similar-sounding words or names. You can then use this value and compare it to another SOUNDEX() using the DIFFERENCE() function. The first character of the SOUNDEX code is the first character of the argument, and the second through fourth characters of the code are numbers. Vowels in the argument are ignored unless they are the first letter of the string. String functions can be nested.

SPACE(value)

This function returns a string of repeated spaces x number of times as indicated by the integer passed in the argument. For example, the statement

 SELECT 'Y' + SPACE(1)+  '=' + SPACE(1) + '1'

returns the expression “Y=1.” If you are adding spaces to Unicode data, use the REPLICATE() function instead of SPACE().

STR(float expression, length, decimal)

This function returns character data converted from numeric data. For example, the statement

 SELECT STR(42393.78, 8, 1)

returns the value “42393.8.” The float expression must be an expression of an approximate numeric (float) data type with a decimal point. The length argument is the total length of the returned value including the decimal point, sign, digits, and spaces. The default is 10. The decimal argument is the number is the number of places to the right of the decimal point, rounded off as in the preceding example.

If you supply the values for length and decimal parameters to the STR() function, they must be positive. The specified length you provide should be greater than or equal to the part of the number before the decimal point plus any number sign you provide. A short float expression is right-aligned in the specified length, while the long float expression is truncated to the specified number of decimal places. For example, STR(12, 10) yields the result of 12, which is right-aligned in the result set. However, STR(1223, 2) truncates the result set to **.

STUFF(string expression, start, length, ch expression)

This function deletes a specified length of characters in a string and “stuffs” another set of characters at a specified starting point. You can use it to delete the characters in the middle of a string and replace them with new characters. For example, the statement

 SELECT STUFF(PHONE,10, 1, '-')   FROM CUSTOMERS

returns all telephone numbers from the PHONE column in the CUSTOMERS table with the space removed at the tenth character and the dash inserted instead. The value is changed from “(800) 555.1212” to “(800) 555–1212”.

SUBSTRING(string expression, start, length)

This function returns part of a character, binary, text, or image expression. For example, the statement

 SELECT SUBSTRING(' (33428-5857)',2,5)

returns the value “33428” representing the first five digits of the nine-digit ZIP code.

The argument in the string expression can be a character string, a binary string, text, an image, a column, or an expression that includes a column (but not an expression that includes aggregate functions). The start parameter is an integer that specifies where the substring begins, while the length parameter takes an integer that specifies the length of the substring (the number of characters or bytes to return). (See Books Online for more information on using this function with the other data types.)

UNICODE(unicode expression)

This function returns the integer value, as defined by the Unicode standard, for the first character of the input expression. For example, the statement

 SELECT UNICODE('§')

returns the value “167” as integer (see also NCHAR()).

UPPER(character expression)

This function returns a character expression with the lowercase characters converted to uppercase. For example, the statement

 SELECT UPPER('noodle')

returns the value “NOODLE” (see also LOWER()) earlier in this section.

Mathematical Functions

The T-SQL mathematical functions are scalar functions that compute the values passed as arguments and then return a numeric value. All the functions are deterministic-in other words, they always return the same value for any given value passed as an argument-with the exception of the RAND() function, which returns a random value. The RAND() function, however, becomes deterministic when you use the same seed value as an argument.

In addition (no pun intended), the trigonometric functions, such as LOG, LOG10, EXP, SQUARE, and SQRT, cast the input value to a float before computing and then return the value as a float. Table 10–11 lists the mathematical functions and provides brief explanations of how to use them. For a complete reference to these functions consult SQL Server Books Online.

Table 10–11: Mathematical Functions
Function	Application
ABS(numeric)	Returns the absolute, positive value of the argument.
ACOS(float)	Returns the angle, in radians, whose cosine is the given argument.
ASIN(float)	Returns the angle, in radians, whose sine is the given float expression (also called arcsine).
ATAN(float)	Returns the angle in radians whose tangent is the given float expression (also called arctangent).
ATN2(float, float)	Returns the angle, in radians, whose tangent is between the two given float expressions (also called arctangent).
CEILING(numeric)	Returns the smallest integer greater than, or equal to, the given numeric expression.
COS(float)	A mathematic function that returns the trigonometric cosine of the given angle (in radians) in the given expression.
COT(float)	A mathematic function that returns the trigonometric cotangent of the specified angle (in radians) in the given float expression.
DEGREES(numeric)	Given an angle in radians, returns the corresponding angle in degrees.
EXP(float)	Returns the exponential value of the given float expression.
FLOOR(numeric)	Returns the largest integer less than or equal to the given numeric expression.
LOG(float)	Returns the natural logarithm of the given float expression.
LOG10(float)	Returns the base-10 logarithm of the given float expression.
PI()	Returns the constant value of PI.
POWER(numeric)	Returns the value of the given expression to the specified power.
RADIANS(numeric)	Returns radians when a numeric expression, in degrees, is entered.
RAND([seed])	Returns a random float value from 0 through 1.
ROUND(numeric, len)	Returns a numeric expression, rounded to the specified length or precision.
SIGN(numeric)	Returns the positive (+1), zero (0), or negative (−1) sign of the given expression.
SIN(float)	Returns the trigonometric sine of the given angle (in radians) in an approximate numeric (float) expression.
SQRT(float)	Returns the square root of the given expression.
SQUARE(float)	Returns the square of the given expression.
TAN(float)	Returns the tangent of the input expression.

Aggregate Functions

The aggregate functions are used to perform a calculation on a set of values and then return a single value to the caller. Typically these values ignore NULL, but COUNT does not because technically NULL is a value. Aggregate functions are often used with the GROUP BY clause in a SELECT statement.

The aggregate functions are deterministic and thus return the same value when they are called with a given set of input values. These functions can only be used in the following situations:

The select list of a SELECT statement-in a subquery or an outer query
In a COMPUTE or COMPUTE BY clause
In a HAVING clause

The Transact-SQL programming language provides these aggregate functions, listed in Table 10–12.

Table 10–12: Aggregate Functions
Function	Application
AVG	Returns the average of the values in a group. Null values are ignored.
CHECKSUM	Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is use for building hash indexes.
CHECKSUM_AGG	Returns the checksum of the values in a group. Null values are ignored.
COUNT	Returns the number of items in a group. COUNT always returns an int data type.
COUNT_BIG	COUNT_BIG always returns a bigint data type value. The only difference between COUNT and COUNT BIG is their return values.
GROUPING	This is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP Grouping is allowed only in the select list associated with a GROUP BY clause that contains either the CUBE or ROLLUP operator.
MAX	Returns the maximum value in the expression.
MIN	Returns the minimum value in the expression.
SUM	Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.
STDEV	Returns the statistical standard deviation of all values in the specified expression.
STDEVP	Returns the statistical standard deviation for the population for all values in the specified expression.
VAR	Returns the statistical variance of all values in the specified expression.
VARP	Returns the statistical variance for the population for all values in the specified expression.

Date and Time Functions

These scalar functions perform an operation on a date and time input value and return a string, numeric, or date and time value. Table 10–13 lists the date and time functions and the information they return.

Table 10–13: Date and Time Functions
Function	Application
DATEADD()	Returns a new datetime value based on adding an interval to the specified date (see Table 16–11).
DATEDIFF()	Returns the number of date and time boundaries crossed between two specified dates.
DATENAME()	Returns a character string representing the specified datepart of the specified date.
DATEPART()	Returns an integer representing the specified datepart of the specified date.
DAY()	Returns an integer representing the day datepart of the specified date.
GETDATE()	Returns the current system date and time in the standard internal format for datetime values.
GETUTCDATE()	Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.
MONTH()	Returns an integer that represents the month part of a specified date.
YEAR()	Returns an integer that represents the year part of a specified date.
Interval	Value	Range
Year	Yy, yy	1753–9999
Quarter	Qq, q	1–4
Month	Mm, m	1–12
Dayofyear	Dd, y	1–366
Day	Dd, d	1–31
Week	Wk, ww	1–53
Weekday	dw	1–7
Hour	hh	0–23
Minute	Mi, n	0–59
Second	ss	0–59
Millisecond	ms	0–999

Text and Image Functions

These are scalar, nondeterministic functions that can perform an operation on a text or image argument. The following functions are supported in T-SQL:

PATINDEX()
TEXTPTR()
TEXTVALID()

PATINDEX(%pattern%, expression)

This function returns the starting position of the first occurrence of a pattern in the specified expression. It returns zeros if the pattern is not found. The function works on all valid text and character data types. For example, the expression

 SELECT PATINDEX('%.%', Phone)  FROM CUSTOMERS

returns 11 records that contain a period in the string. Nine of these are reported to be in position three, which indicates that an IP address has been inserted into the Phone column. Records that do not qualify are returned as a zero value on the returned result set. In the preceding example, I used a single character as an example, but your pattern could be any combination of characters and spaces that form the pattern. In other words, the pattern is a literal string.

You can also use wildcard characters, but you must remember to insert the % character at the beginning and end of the pattern to be evaluated (except when searching for first or last characters). You can also use PATINDEX() in a WHERE clause. For example, the following statement

 WHERE (SELECT PATINDEX('%Shapiro%', LastName)   FROM CUSTOMERS)

returns all customers with a last name of Shapiro in the CUSTOMERS table.

TEXTPTR(column)

This function returns the text-pointer value that corresponds to a text, ntext, or image column in varbinary format. The retrieved text pointer value can then be used in READTEXT, WRITETEXT, and UPDATE statements. For example, the statement

 DECLARE @pointer varbinary(16) SELECT @ pointer = TEXTPTR(image) FROM articles a, publications p WHERE a.pubdate = p.pubdate    AND p.runtitle = 'Daily News'

returns the image data you can then use in the client application.

For tables with in-row text, TEXTPTR returns a handle for the text to be processed. You can obtain a valid text pointer even if the text value is null. If the table does not have in-row text, and if a text, ntext, or image column has not been initialized by an UPDATE statement, TEXTPTR returns a null pointer.

TEXTVALID()

The TEXTVALID() function is used to check whether a text pointer exists. You cannot use UPDATETEXT, WRITETEXT, or READTEXT without a valid text pointer. Chapter 17 provides an example of the TEXTVALID() function.

Conversion Functions

SQL Server 2005 supports two conversion functions, CONVERT() and CAST(), that let you convert a variable or column of one type to another. Use of these functions is called explicit casting or conversion because SQL Server 2005 supports automatic conversion on several data types. In other words, the conversion functions can be used if you have no choice but to manually convert, or your application demands it for some reason.

CAST() does not do anything more than CONVERT(), but it is provided for compatibility with the SQL-92 standard. This discussion thus focuses on CONVERT(), and I will make mention of features that CAST() does not support. The syntax for this function is CONVERT(data_type, variable, style). The arguments are required as follows:

Data_type This is the target of the conversion, for example to convert a money value to a character value data type for use in the construction of a financial report, perhaps an invoice.
Variable This is the variable to convert to or the object of the conversion.
Style This is the optional variable when the target data type can take one or more style changes.

You can use either of the functions in SELECT statements, and in the WHERE clause, and anywhere else you provide an expression. The following example converts a column from 30 to 25 characters:

 SELECT CONVERT(nchar(30), StockName)

The data type argument you use in CONVERT() can be any valid data type supported by SQL Server. If you use a data type that takes a length argument (nchar, nvarchar, char, varchar, binary, or varbinary), then you can pass its length in the parentheses that enclose the data type length.

You can also use the CONVERT() function to obtain a variety of special data formats. For example, the style argument (not supported by CAST()) is used to specify a particular date format required when you convert datetime and smalldatetime variables to character types. Table 10–14 lists the style values and the date formats returned.

Table 10–14: Values for Datetime
Value sans Century	Value con Century	Date Format
1	101	mm/dd/yy
2	102	yy.mm.dd
3	103	dd/mm/yy
4	104	dd.mm.yy
5	105	dd-mm-yy
6	106	Dd mon yy
7	107	Mon dd, yy
8	108	Hh:mm:ss
9	109	Mon dd yyyy hh:mi:ss:mmm AM or PM
10	110	mm-dd-yy
11	111	Yy/mm/dd
12	112	Yymmdd
13	113	Dd mm yyy hh:mm:ss:mmm(24)
14	114	Hh:mi:ss:mmm(24)
20	120	Yyyy-mm-dd hh:mm:ss(24)
21	121	Yyy-mm-dd hh:mi:ss:mmm(24)

The following example illustrates the differences between CAST() in the first SELECT statement and CONVERT() in the second SELECT statement. The result set is the same for both queries:

CAST():

 USE NORTHWIND  SELECT CAST(regiondescription AS char(2)), regionid  FROM region

CONVERT():

 SELECT CONVERT(char(2), regiondescription), regionid FROM region

The result set is the same for both queries:

 Ea 1 We 2 No 3 So 4

In the preceding example, we converted the region description column from 50 to 2 characters. A better example would be to convert a first name column to one character and compile a report listing of first name initials and full last names. CONVERT() is also useful when using LIKE in the WHERE clause.

As mentioned earlier, SQL Server automatically converts certain data types. If, for example, you compare a char expression and a datetime expression, or a smallint expression and an int expression, or char expressions of different lengths, SQL Server will convert them automatically. This is called an implicit conversion.

Expect the following behavior from the conversion functions:

SQL Server reports an error when you attempt a conversion that is not possible. For example, trying to converting a char with letters to an integer will create an exception.
If you do not specify a length when converting, SQL Server will supply a length of 30 characters by default.
SQL Server will reject all values it cannot recognize as dates (including dates earlier than January 1, 1753) when you try to convert from datetime or smalldatetime. You can only convert datetime to smalldatetime when the date is in the proper date range (from January 1, 1900, through June 6, 2079). The time value will be rounded to the nearest minute.
Converting to a bit will change any nonzero value to 1.
When you convert to money or smallmoney, any integers in the conversion expression are assumed to be monetary units. For example, let’s say you pass an integer value of 5 in the expression; SQL Server will convert it to the money equivalent of five dollars-expressed as U.S. dollars if us_english is the default language.
All money value numbers to the right of the decimal in floating-point values are rounded to four decimal places by default. Expressions of data types char or varchar that are being converted to an integer data type must consist only of digits and an optional plus or minus sign (+ or −). The leading blanks are ignored. Any expressions of data types char or varchar converted to money can also include an optional decimal point and leading currency sign.
You can include optional exponential notation (e or E, followed by an optional + or − sign, and then a number) in data types char or varchar that are being converted to float or real.
When you pass character strings for conversion to a data type of a different size, any values too long for the new data type are truncated, and SQL Server displays an asterisk (*). This is the default display in both the OSQL utility and Management Studio. Any numeric expression that is too long for the new data type to display is truncated.
You can also explicitly convert any text data to char or varchar, and image data to binary or varbinary. As discussed earlier, these data types are limited to 8,000 characters, and so you are limited to the maximum length of the character and binary data types; that is, 8,000 characters. When you explicitly convert ntext data to nchar or nvarchar, the output is confined to the maximum length of 4,000 characters. Remember that when you do not specify the length, the converted value has a default length of 30 characters. Implicit conversion is not supported with these functions.
When you convert between data types in which the target data type has fewer decimal places than the source data type, the resulting value is truncated. For example, the result of CAST(10.3496 AS money) is $10.3496.

Style

The number you supply as the style argument is used to determine how the datetime data will be displayed. For starters, the year can be displayed in either two or four digits. By default, SQL Server supplies a two-digit year, which may be a problem in certain transactions. For example, the statement

 SELECT CONVERT(char(50), GETDATE(), 101)

returns the date 07/06/2005, while the statement

 SELECT CONVERT(char(50), GETDATE(), 1)

returns the date 07/06/00. Table 10–14 provides the values for the style argument.