A Review of BuiltFunctions 

BuiltIn Functions Fundamentals 
Introduction 
While your primary job as a database developer consists of creating lists, probably your second most important job is to assist your users with the various assignments they must perform on your application. One way you can assist is to use functions that perform otherwise complex tasks. We introduced and described functions in the previous lesson. To assist your development with the different tasks of a database, TransactSQL ships with various already created and tested functions. You just need to be aware of these functions, their syntax, and the results they produce. To help you identify the functions you can use, they are categorized by their types and probably their usefulness. 
Because of their complexities, some values can be easily recognized or fixed. For example, a date such as January 6, 1995 is constant and can never change. This type of value is referred to as deterministic because it is always the same. In the same way, a time value such as 5PM is constant and cannot change. There are other values that cannot be known in advance because they change based on some circumstances. For example, the starting date of the school year changes from one year to another but it always occurs. This means that, you know it will happen but you don't know the exact date. Such a value is referred to as nondeterministic. To support determinism and nondeterminism, TransactSQL provides two broad categories of functions. A function that always returns the same or known value is referred to as deterministic. A function whose returned value may depend on a condition is referred to as nondeterministic. In most cases, a value the user submits to your database is primarily considered a string. This is convenient if that's what you are expecting. If the value the user provides must be treated as something other than a string, for example, if the user provides a number, before using such a value, you should first convert it to the appropriate type, that is, from a string to the expected type. To assist with conversion, you can use either the CAST() or the CONVERT() function. The syntax of the CAST() function is: CAST(Expression AS DataType) The Expression is the value that needs to be cast. The DataType factor is the type of value you want to convert the Expression to. The DataType can be one of those we reviewed in Lesson 4. In the following example, two variables are declared and initialzed as strings. Because they must be involved in a multiplication, each is converted to a Decimal type: DECLARE @StrSalary Varchar(10), @StrHours Varchar(6), @WeeklySalary Decimal(6,2) SET @StrSalary = '22.18'; SET @StrHours = '38.50'; SET @WeeklySalary = CAST(@StrSalary As Decimal(6,2)) * CAST(@StrHours As Decimal(6,2)); SELECT @WeeklySalary; GO Here is an example of executing the above statement:
Like CAST(), the CONVERT() function is used to convert a value. Unlike CAST(), CONVERT can be used to convert a value its original type into a nonsimilar type. For example, you can use CONVERT to cast a number into a string and viceversa. The syntax of the CONVERT() function is: CONVERT(DataType [ ( length ) ] , Expression [ , style ]) The first argument must be a known data type, such as those we reviewed in Lesson 4. If you are converting the value into a string (varchar, nvarchar, char, nchar) or a binary type, you should specify the number of allowed characters the data type's own parentheses. As reviewed for the CAST() function, the Expression is the value that needs to be converted. Here is an example:  Square Calculation DECLARE @Side As Decimal(10,3), @Perimeter As Decimal(10,3), @Area As Decimal(10,3); SET @Side = 48.126; SET @Perimeter = @Side * 4; SET @Area = @Side * @Side; PRINT 'Square Characteristics'; PRINT ''; PRINT 'Side = ' + CONVERT(varchar(10), @Side, 10); PRINT 'Perimeter = ' + CONVERT(varchar(10), @Perimeter, 10); PRINT 'Area = ' + CONVERT(varchar(10), @Area, 10); GO This would produce:
The string is the most basic, the primary value that is presented to a database. This is because, any value, before being treated particularly, is firstly considered a string. In an application, there are various ways you use or get a string. You can get it or provide it to a function as a constant string, that is, a string whose value you know certainly and that you pass to a function. You can also get a string that a user provides. Other functions also can produce or return a string. To assist you with managing strings or performing operations on them, TransactSQL provides various functions. The functions can divide in categories that include characterbased, conversions, addition, substrings, etc.
Some operations performed on strings require that you know the number of characters of a string. This is because some operations require a minimum number of characters and some other functions require that the string have at least one character. The number of characters of a string is also called the length of the string. To get the length of a string, you can use the LEN() function. Its syntax is: int LEN(String) This function takes one argument as the string to be considered. It returns the number of characters in the string. Here is an example: DECLARE @FIFA varchar(120) SET @FIFA = 'Fédération Internationale de Football Association' SELECT @FIFA AS FIFA SELECT LEN(@FIFA) AS [Number of Characters] This would produce: As you may know already, a string is primarily one or a group of characters. These characters are ASCII values. If you have a string, to get the ASCII code of its leftmost character, you can use the ASCII() function. Its syntax is: int ASCII(String) This function takes as argument as string and returns the ASCII code of the first (the left) character of the string. Here is an example: DECLARE @ES varchar(100) SET @ES = 'El Salvador' SELECT @ES AS ES SELECT ASCII(@ES) AS [In ASCII Format] This would produce: If you have the ASCII code of a character and want to find its actual character, you can use the CHAR() function. Its syntax is: char CHAR(int value) This function takes as argument a numeric value as an integer. Upon conversion, the function returns the ASCII equivalent of that number. As you may know already, a string can be made of uppercase, lowercase, and symbols that don't have a particular case. When you receive a string, if you want to convert all of its characters to lowercase, you can use the LOWER() function. Its syntax is: varchar LOWER(String) This function takes as argument a string. Any lowercase letter that is part of the string would not change. Any letter that is part of the string would be converted to lowercase. Any other character or symbol would be kept "as is". After conversion, the LOWER() function returns a new string. Here is an example: DECLARE @FIFA varchar(120) SET @FIFA = 'Fédération Internationale de Football Association' SELECT @FIFA AS FIFA SELECT LOWER(@FIFA) AS Converted This would produce:
A substring is a section gotten from a string. The idea is to isolate one or a group of characters for any necessary reason. A left substring is one or a group of characters retrieved from the left side of a known string. To get the left substring of a string, you can use the LEFT() function. Its syntax is: varchar LEFT(String, NumberOfCharacters) This function takes two arguments. The first argument specifies the original string. The second argument specifies the number of characters from the mostleft that will constitute the substring. After the operation, the LEFT() function returns a new string made of the left character + the NumberOfCharacters on its right from the String.
Instead of the starting characters of a string, you may want to create a string using the mostright characters of an existing string. To support this operation, TransactSQL provides the RIGHT() function. Its syntax is: varchar RIGHT(String, NumberOfCharacters) This function takes two arguments. The first argument specifies the original string. The second argument specifies the number of characters from the mostright that will constitute the substring.
One of the most annoying situations you may encounter with a string is to deal with one that contains unexpected characters. This could be due to its formatting or any other reason. For example, if you request a telephone number from a user, there are various ways the string could be presented to you. Examples are 0000000000, or 0000000000, or (000) 0000000. Every one of these formats is an acceptable US and Canadian telephone number but if you involve that string in an operation, you could get an unpredictable result. One way you can solve this type of problem is to remove any undesired characters from the string. This operation can also consist of replacing some character(s) with other(s). To replace one character or a substring from a string, you can use the REPLACE() function. Its syntax is: varchar REPLACE(String, FindString, ReplaceWith) or binary REPLACE(String, FindString, ReplaceWith) This function takes three arguments. The first is the string that will be used as reference. The second argument, FindString, is a character or a substring to look for in the String argument. If the FindString character or substring is found in the String, then it is replaced with the value of the last argument, ReplaceWith.
In arithmetic, a number is considered as being negative (less than 0), null (equal to 0), or positive (higher than 0). When a number is negative, it must have a  symbol to its left. If it is positive, it may display a + symbol to its left or it can omit it. A number without the  or + symbol to its left is considered positive, also referred to as unsigned. The symbol that determines whether a number is positive or negative is referred to as its sign. The sign is easily verifiable if you know the number already. In some cases, when a number is submitted to your application, before taking any action, you may need to get this piece of information. To find out if a value is positive, null, or negative, TransactSQL provides the SIGN() function. Its syntax is: SIGN(Expression) This function takes as argument a number or an expression that can be evaluated to a number. The interpreter would then examine the number:
Based on this, you can use the SIGN() function to find out whether a value is negative, null, or positive: simply pass the value (or a variable) to SIGN() and use a logical operator to check its sign. Here is an example:  Square Calculation DECLARE @Side As Decimal(10,3), @Perimeter As Decimal(10,3), @Area As Decimal(10,3); SET @Side = 48.126; SET @Perimeter = @Side * 4; SET @Area = @Side * @Side; IF SIGN(@Side) > 0 BEGIN PRINT 'Square Characteristics'; PRINT ''; PRINT 'Side = ' + CONVERT(varchar(10), @Side, 10); PRINT 'Perimeter = ' + CONVERT(varchar(10), @Perimeter, 10); PRINT 'Area = ' + CONVERT(varchar(10), @Area, 10); END; ELSE PRINT 'You must provide a positive value'; GO Here is an example of executing the statement: Here is another example of executing the statement: Notice that the interpreter acts differently in response to the result of the SIGN() function.
The decimal numeric system counts from minus infinity to infinity. This means that numbers are usually negative or positive, depending on their position from 0, which is considered as neutral. In some operations, the number considered will need to be only positive even if it is provided in a negative format. The absolute value of a number x is x if the number is (already) positive. If the number is negative, its absolute value is its positive equivalent. For example, the absolute value of 12 is 12, while the absolute value of –12 is 12. To get the absolute value of a number, you can use the ABS() function. Its syntax is: ABS(Expression) This function takes an expression or a number as argument and returns its absolute value. Here is an example: DECLARE @NumberOfStudents INTEGER; SET @NumberOfStudents = 32; SELECT ABS(@NumberOfStudents) AS [Number of Students]; GO This would produce:
Consider a decimal value such as 12.155. This number is between integer 12 and integer 13 In the same way, consider a number such as –24.06. As this number is negative, it is between –24 and –25, with –24 being greater. In algebra, the ceiling of a number is the closest integer that is greater than or higher than the number considered. In the first case, the ceiling of 12.155 is 13 because 13 is the closest integer greater than or equal to 12.155. The ceiling of –24.06 is –24. To get the ceiling of a number, TransactSQL provides the CEILING() function. Its syntax is: CEILING(Expression) This function takes as argument a number or an expression that can evaluate to a number. After the conversion, if the function succeeds, it returns a doubleprecision number that is greater than or equal to Expression. Here is an example: DECLARE @Number1 As Numeric(6, 2), @Number2 As Numeric(6, 2) SET @Number1 = 12.155; SET @Number2 = 24.06; SELECT CEILING(@Number1) AS [Ceiling of 12.155], CEILING(@Number2) AS [Ceiling of –24.06]; GO This would produce: Here is another way of displaying the above results: DECLARE @Number1 As Numeric(6, 2), @Number2 As Numeric(6, 2) SET @Number1 = 12.155; SET @Number2 = 24.06; PRINT 'The ceiling of 12.155 is ' + CONVERT(varchar(10), CEILING(@Number1)); PRINT 'The ceiling of –24.06 is ' + CONVERT(varchar(10), CEILING(@Number2)); GO This would produce:
Consider two decimal numbers such as 128.44 and 36.72. The number 128.44 is between 128 and 129 with 128 being the lower. The number –36.72 is between –37 and –36 with –37 being the lower. The lowest but closest integer value of a number is referred to as its floor. Based on this, the floor of 128.44 is 128. The floor of –36.72 is –37. To support finding the floor of a number, TransactSQL provides the FLOOR() function. Its syntax is: FLOOR(Expression) The FLOOR() function takes as argument a numeric value or an expression that can be evaluated to a number. If the function succeeds during its conversion, it produces the integer that is the floor of the argument. Here is an example: DECLARE @Number1 As Numeric(6, 2), @Number2 As Numeric(6, 2); SET @Number1 = 128.44; SET @Number2 = 36.72; SELECT FLOOR(@Number1) AS [Floor of 128.44], FLOOR(@Number2) AS [Floor of –36.72]; GO This would produce:
To calculate the exponential value of a number, TransactSQL provides the EXP() function. Its syntax is: EXP(Expression) This function takes one argument as a number or an expression that can be evaluated to a number. Here is an example: DECLARE @Number As Numeric(6, 2); SET @Number = 6.48; SELECT EXP(@Number) AS [Exponent of 6.48]; GO This would produce:
The power of a number is the value of that number when raised to another number. This is done using the following formula: ReturnValue = x^{y} To support finding the power of a number, TransactSQL provides the POWER() function. Its syntax is: POWER(x, y) This function takes two required arguments. The first argument, x, is used as the base number to be evaluated. The second argument, y, also called the exponent, will raise x to this value. Here is an example: DECLARE @x As Decimal(6, 2), @y As Decimal(6, 2); SET @x = 20.38; SET @y = 4.12; SELECT POWER(@x, @y) AS [Power of 20.38 raised to 4.12]; GO This would produce:
To assist with finding the natural logarithm of a number, TransactSQL provides the LOG() function. Its syntax is: LOG(Expression) This function takes one argument as a number or an expression that can evaluate to a number. After the calculation, it returns the natural logarithm of the argument. Here is an example: DECLARE @Number As Decimal(6, 2); SET @Number = 48.16; SELECT LOG(@Number) AS [Natural Logarithm of 48.16]; GO This would produce:
To calculate the base 10 logarithm of a number, TransactSQL provides the LOG10() function. Its syntax is: LOG10(Expression) The number to be evaluated is passed as the argument X. The function returns the logarithm on base 10 using the formula: y = log10x which is equivalent to x = 10^{y} Here is an example: DECLARE @Number As Decimal(6, 2); SET @Number = 48.16; SELECT LOG10(@Number) AS [Base10 Logarithm of 48.16]; GO This would produce:
To support the calculation of a square root, TransactSQL provides the SQRT() function. Its syntax is: SQRT(Expression) This function takes one argument as a positive decimal number. If the number is positive, after the calculation, the function returns the square root of x. Here is an example: DECLARE @Number As Decimal(6, 2); SET @Number = 48.16; SELECT SQRT(@Number) AS [The square root of 48.16 is]; GO This would produce:
If the number is negative, you would receive an error. Here is an example:
In this case, you can use a control statement to find out whether the Expression is positive. Here is an example: DECLARE @Number As Decimal(6, 2); SET @Number = 258.4062; IF SIGN(@Number) > 0 PRINT 'The square root of 258.4062 is ' + CONVERT(varchar(12), SQRT(@Number)); ELSE PRINT 'You must provide a positive number'; GO Here is one example of executing the statement:
Here is another example of executing the statement:
A circle is a series of distinct opposite points positioned each at an exact same distance from another point referred to as the center. The distance from the center C to one of these equidistant points is called the radius, R. The line that connects all of the points that are equidistant to the center is called the circumference of the circle. The diameter is the distance between two points of the circumference to the center. In other words, a diameter is double the radius. To manage the measurements and other related operations, the circumference is divided into 360 portions. Each of these portions is called a degree. The unit used to represent the degree is the degree, written as ˚. Therefore, a circle contains 360 degrees, that is 360˚. The measurement of two points A and D of the circumference could have 15 portions of the circumference. In this case, this measurement would be represents as 15˚. The distance between two equidistant points A and B is a round shape geometrically defined as an arc. An angle is the ratio of the distance between two points A and B of the circumference divided by the radius R. This can be written as:
The letter п, also written as PI, is a number used in various mathematical calculations. Its approximate value is 3.1415926535897932. The calculator of Microsoft Windows represents it as 3.1415926535897932384626433832795. To get the value of PI, TransactSQL provides the PI() function. Its syntax is simply: PI()
An angle is the ratio of an arc over the radius. Because an angle is a ratio and not a “physical” measurement, which means an angle is not a dimension, it is independent of the size of a circle. Obviously the angle represents the number of portions covered by three points. A better unit used to measure an angle is the radian or rad. If you know the value of an angle in degrees and you want to get the radians, TransactSQL provides the RADIANS() function. Its syntax is: RADIANS(Expression) This function takes as argument a value in degrees. If it succeeds in its calculation, it returns the radians value. A cycle is a measurement of the rotation around the circle. Since the rotation is not necessarily complete, depending on the scenario, a measure is made based on the angle that was covered during the rotation. A cycle could cover part of the circle, in which case the rotation would not have been completed. A cycle could also cover the whole 360˚ of the circle and continue there after. A cycle is equivalent to the radian divided by 2 * Pi.
If you know the radians but want to get the degrees of an angle, you can use the DEGREES() function. Its syntax is: DEGREES(Expression) This function takes as argument a value in radians. If it succeeds, it returns the equivalent value in degrees.
Consider AB the length of A to B, also referred to as the hypotenuse. Also consider AC the length of A to C which is the side adjacent to point A. The cosine of the angle at point A is the ratio AC/AB. That is, the ratio of the adjacent length, AC, over the length of the hypotenuse, AB: The returned value, the ratio, is a doubleprecision number between –1 and 1. To get the cosine of an angle, you can call the COS() function. Its syntax is: COS(Expression) The angle to be considered is passed as the argument to this function. The function then calculates and returns its cosine. Here is an example: DECLARE @Angle As Decimal(6, 3); SET @Angle = 270; SELECT COS(@Angle) AS [Cosine of 270]; GO This would produce:
Consider AB the length of A to B, also called the hypotenuse to point A. Also consider CB the length of C to B, which is the opposite side to point A. The sine represents the ratio of CB/AB; that is, the ratio of the opposite side, CB over the hypotenuse AB. To get the sine of an angle, you can use the SIN() function whose syntax is: SIN(Expression) The angle to be considered is passed as the argument. After its calculation, the function returns the sine of the angle between –1 and 1. Here is an example: DECLARE @Angle As Decimal(6, 3); SET @Angle = 270; SELECT SIN(@Angle) AS [Sine of 270]; GO This would produce:
In geometry, consider AC the length of A to C. Also consider BC the length of B to C. The tangent is the result of BC/AC, that is, the ratio of BC over AC. To get the tangent of an angle, you can use the TAN() function of TransactSQL. Its syntax is: TAN(Expression) Here is an example: DECLARE @Angle As Decimal(6, 3); SET @Angle = 270; SELECT TAN(@Angle) AS [Tangent of 270]; GO This would produce:
Date and time values are highly used in database applications. They involve sales, time sheets, taxes, overtime work, etc. Based on this usefulness, their operations are supported by various libraries you will be using when developing your application. Without being the most elaborate on this issue, TransactSQL provides its own level of support for date and time values. Before using a date or a time value in a calculation, remember that you must first get it one way or another. You can define a date or a time constant in your application. An example would be '1992/10/28'. You can declare a DateTime or a SmallDateTime variable and initialize it as you see fit. You may get a date or a time from another function. As the last alternative, you may get a date or time from another application or from a user. Once you have an appropriate date, you can use it. One of the ways you can assist the user with date and time is to get the current date or the current time. For example, if you create a time sheet, when the user starts using it, it would be convenient to fill part of the time sheet with such predictable values. To get the current date and the current time of the computer that a user is using, you can use the GETDATE() function of TransactSQL. Its syntax is: GETDATE() This function simply returns the current date and time of the operating system.
One of the primary operations you may want to perform on a date or a time value would consist of adding a value to it. To support this operation, TransactSQL provides the DATEADD() function. Its syntax is: DATEADD(TypeOfValue, ValueToAdd, DateOrTimeReferenced) The third argument to this function is the value of a date or a time on which the operation will be performed. It can be a constant value in the form of 'year/month/day' for a date or 'hour:minutes AM/PM' for a time. The second argument is the value that will be added. It should be a constant integer, such as 8, or a floating point value, such as 4.06. When calling this function, you must first specify the type of value that you want to add. This type is passed as the first argument. It is used as follows:
In the same way, you can add values as follows:
Another regular operation performed on a date or a time value consists of getting the number of units that has elapsed in the range of two dates or two time values. To support this operation, TransactSQL provides the DATEDIFF() function. Its syntax is: DATEDIFF(TypeOfValue, StartDate, EndDate) This function takes three arguments. The second argument is the starting date or the starting time of the range to be considered. The third argument is the end or last date or time of the considered range. You use the first argument to specify the type of value you want the function to produce. This argument uses the same value as those of the DATEADD() function:
Here is an example that calculates the number of years that an employees has been with the company: DECLARE @DateHired As DateTime,; @CurrentDate As DateTime; SET @DateHired = '1996/10/04'; SET @CurrentDate = GETDATE(); SELECT DATEDIFF(year, @DateHired, @CurrentDate) AS [Current Experience]; GO This would produce:



Previous  Copyright © 20072008 FunctionX, Inc.  Next 
