Web Hosting Java Blog

Chapter 4 QUERYING AND MODIFYING DATA 225 Sometimes data is entered into a database with leading or trailing spaces. To retrieve data free of these spaces, use the trim functions: LTRIM and RTRIM. SELECT RTRIM (LTRIM( this is an example ))+ TESTER As example Concatenation processes are often performed to generate names from a fully normalized database, as shown in Figure 4.5. You can change the direction of text or make an expression uppercase or lowercase by using these three functions: REVERSE, UPPER, and LOWER. Look at the following example: SELECT UPPER( mustafa ), LOWER( MUNAWAR ), REVERSE( Mustafa ) Concatenation of a string value to a NULL string results in NULL. A check should be performed for string content in situations where concatenated information could be partially or completely NULL. The SUBSTRING Function It may be that you want to return a part of a string rather than the whole string. The SUBSTRING function, which returns a part of a character or binary string, can be used for this. The syntax for SUBSTRING is SUBSTRING (expression, start, length) FIGURE 4.5 Concatenation to formulate names from multiple columns.

For high quality jboss hosting services please check jboss web hosting website.

224 Part I EXAM PREPARATION Character Functions SQL Server also provides a full array of character (string) functions. Character functions enable you to easily manipulate attributes that are character-specific. Most of the functions provided in table are normally used and therefore you should regularly practice using them. For example, if you want to not include the blank spaces before a value or after a value, you could use the LTRIM and RTRIM functions. Table 4.9 is a compilation of the various character functions included in SQL Server s version of SQL. TABLE 4.9 CHARACTER FUNCTIONS IN T-SQL Function Parameters Result + Expression1 + Concatenates a string; brings two strings expression2 together. ASCII String expression Gets the ASCII code value of the string. CHAR Integer expression Changes the ASCII integer into a character. LEN String expression Identifies the length of an expression in characters. LOWER String expression Converts uppercase to lowercase. LTRIM String expression Removes leading blanks. PATINDEX Pattern, expression Returns the position of the beginning of the occurrence of the pattern. If the pattern is not found, it returns a zero. REPLICATE String expression, Repeats a character expression the integer expression number of times specified. REVERSE String expression Returns the reverse of a string expression. RTRIM String expression Removes trailing blanks. SPACE Integer expression Returns a string of repeated spaces. STUFF String expression1, Deletes a specified length of characters start, length, String and inserts another set of characters at expression2 a specified starting point. SUBSTRING String expression, Returns part of a string expression. start, length UPPER String expression Makes all lowercase letters uppercase.

For reliable and cheap web hosting services please check tomcat web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 223 TABLE 4.8 continued MATHEMATICAL FUNCTIONS IN T-SQL Function Parameters Result LOG (Float expression) Natural logarithm of the specified expression. LOG10 (Float expression) Base 10 logarithm of the specified expression. PI ( ) Returns 3.1415926535897931. POWER (Numeric expression, y) Value of numeric expression to the power of y. RADIANS (Numeric expression) Radians converted from degrees of the numeric expression. RAND (seed ) Random float value between zero and one. ROUND (Numeric expression, Numeric expression rounded off to length) the precision (length) of a number. SIGN (Numeric expression) Returns the positive, negative, or zero sign of a numeric expression. SIN (Float expression) Trigonometric sine of the specified angle. SQRT (Float expression) Square root of the specified expression. TAN (Float expression) Tangent of the specified expression. SQUARE (Float expression) Square of the specified expression. The following example shows you a query that uses some of the many mathematical functions in SQL Server. Notice that when a query is non-data retrieval, as this one is, a FROM clause does not have to be specified. SELECT pi() AS PI , abs(-44) AS Absolute , power(2,3) AS Power Discussion of mathematical functions would be an involved endeavor for this medium. this blog does not set out to make mathematical geniuses out of every SQL developer. To become fully versed in all mathematical functions would require a significant education in the science of mathematics. Character functions interact against alphanumeric data in a similar fashion as numeric data interacts with mathematical functions.

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

222 Part I EXAM PREPARATION tinyint). The precision of built-in operations on float data type data is six decimal places by default. By default, a number passed to a mathematical function is interpreted as a decimal data type. The CAST or CONVERT functions can be used to change the data type to something else, such as a float. For example, the value returned by the FLOOR function has the data type of the input value. The input of this SELECT statement is a decimal, and FLOOR(123.34) returns 123, which is a decimal value. You can use the diverse set of math functions provided by T-SQL to fulfill many database needs. SQL Server provided numerous mathematical functions so that you can perform the most complex calculations possible. These numerous functions are provided in most programming language environments. A compiled list of most of the important math functions is listed in Table 4.8. TABLE 4.8 MATHEMATICAL FUNCTIONS IN T-SQL Function Parameters Result ABS (Numeric expression) Returns the absolute value of a number. ACOS (Float expression) Angle (in radians) whose cosine is the specified float expression. ASIN (Float expression) Angle (in radians) whose sine is the specified float expression. ATAN (Float expression) Angle (in radians) whose tangent is the specified float expression. ATN2 (Float expression1, Returns the angle in radians float expression2) whose tangent is between the two given float expressions (also called arctangent). CEILING (Numeric expression) Returns the smallest integer greater than or equal to the numeric expression. COS (Float expression) The cosine of the specified expression. COT (Float expression) Cotangent of the specified expression. DEGREES (Numeric expression) Degrees converted from radians of the numeric expression. EXP (Float expression) Exponential value of the specified expression. FLOOR (Numeric expression) Largest integer less than or equal to the specified numeric expression. continues

For high quality website hosting services please check cheap web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 221 Where: date_part determines which unit of time measure needs to be found. The values it can take were shown in Table 4.7. date is an expression denoting the date being used. Here s an example of using DATENAME to find the name of the current month: Select datename(mm,getdate()) as month The DATEPART and DATENAME functions produce the specified part of a datetime value (the year, quarter, day, hour, and so on) as either an integer or a string. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used with either of these functions, the seconds and milliseconds returned are zero. DATEPART DATEPART is similar to DATENAME except that it returns an integer value indicating the date rather than a character string. The syntax for DATEPART is the same as for DATENAME. The following is an example using DATEPART to find the current month and display it numerically: SELECT datepart(mm,getdate()) AS month Many useful date functions give the developer flexibility in dealing with what is traditionally one of the more pesky data types: Dates. With history reflecting unfavorably on the storage of date information, it is important to consider this data type to be infinite in that dates need to remain unique as time progresses. Numeric data types have always had useful functionality supplied with the programming environment in most languages. SQL Server is no exception with its adoption of a standard set of mathematical functions. Using Mathematical Functions in T-SQL A mathematical function performs a math operation on numeric expressions and returns the result of the operation. Math functions operate on any of the SQL Server numeric data types (decimal, integer, float, real, money, smallmoney, smallint, and

For high quality website hosting services please check cheap web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 221 Where: date_part determines which unit of time measure needs to be found. The values it can take were shown in Table 4.7. date is an expression denoting the date being used. Here s an example of using DATENAME to find the name of the current month: Select datename(mm,getdate()) as month The DATEPART and DATENAME functions produce the specified part of a datetime value (the year, quarter, day, hour, and so on) as either an integer or a string. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used with either of these functions, the seconds and milliseconds returned are zero. DATEPART DATEPART is similar to DATENAME except that it returns an integer value indicating the date rather than a character string. The syntax for DATEPART is the same as for DATENAME. The following is an example using DATEPART to find the current month and display it numerically: SELECT datepart(mm,getdate()) AS month Many useful date functions give the developer flexibility in dealing with what is traditionally one of the more pesky data types: Dates. With history reflecting unfavorably on the storage of date information, it is important to consider this data type to be infinite in that dates need to remain unique as time progresses. Numeric data types have always had useful functionality supplied with the programming environment in most languages. SQL Server is no exception with its adoption of a standard set of mathematical functions. Using Mathematical Functions in T-SQL A mathematical function performs a math operation on numeric expressions and returns the result of the operation. Math functions operate on any of the SQL Server numeric data types (decimal, integer, float, real, money, smallmoney, smallint, and

For reliable and cheap web hosting services please check cheap web hosting website.

220 Part I EXAM PREPARATION DATEDIFF subtracts the start date from the end date to produce the result. Look at the following example: USE pubs GO SELECT pubdate, DATEDIFF(year, pubdate, getdate()) AS . years difference FROM titles The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of date_part boundaries crossed between the first and second date. For example, the number of weeks between Monday, March 5, and Monday, March 12, is 1. GETDATE To get the current date, use the GETDATE function. This function can be useful when you are producing reports that need to be dated. The GETDATE function returns the current system date and can be used in a SELECT statement. The syntax for GETDATE is GETDATE() The following is an example of using GETDATE() to return the current date: SELECT Getdate() as today s date You can use GETDATE in designing a report to have the current date and time printed every time the report is produced. GETDATE is also useful for functions such as logging the time a transaction occurred on an account. You can use GETDATE anywhere to return the current system date. DATENAME The DATENAME function can be used in determining the current date_part in the form of a string. You may, for instance, need to know specifically the day or month of a publication. The DATENAME function returns a character string representing the specified date part from the date. The syntax is DATENAME (date_part, date)

For high quality website hosting services please check java web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 219 TABLE 4.7 POSSIBLE VALUES FOR DATE_PART date_part name Abbreviations Year yy, yyyy Quarter qq, q Month mm, m Dayofyear dy, y Day dd, d Week wk, ww Hour hh Minute mi, n Second ss, s Millisecond ms The DATEDIFF function is used in a similar manner. This function, however, calculates the amount of time in dateparts between the second and first of two dates you specify. DATEDIFF Included in the various date manipulation functions is DATEDIFF. As the name suggests, DATEDIFF is a function that returns the difference between two date parts specified. The syntax for DATEDIFF is DATEDIFF (date_part, start_date, end_date) Where: date_part is the unit of time measure you want to find the difference between. The values this can take are shown earlier in Table 4.7. start_date is the beginning date for the calculation. end_date is the date being subtracted from start_date.

For reliable and cheap web hosting services please check cheap web hosting website.

218 Part I EXAM PREPARATION Be careful with the use of appropriate functions because nondeterministic functions cannot be used in a lot of processes. An index cannot be created on a computed column if the expression references any nondeterministic functions, and a clustered index cannot be created on a view if the view references any nondeterministic functions. Functions can be divided into categories that are each addressed separately over the remainder of this exam objective coverage. Date Functions In T-SQL, as with many other programming languages, date manipulation has been given special importance, by the many functions available. Dates are implemented as datetime and smalldatetime data types and need to be treated with special functions such as DATEDIFF. Dates cannot be added or subtracted using the regular operators. The date functions available in SQL Server enable you to change and manipulate dates easily; the dates must be in datetime or smalldatetime type. The next couple of sections cover the date functions found in SQL Server. DATEADD The DATEADD function adds a number to the part of the date you specify, such as month or day, and then returns the new datetime value. The syntax is DATEADD (date_part, number, date) Where: Date_part determines which unit of measure of time. A listing of the possible values accepted is shown in Table 4.7. Number determines the number by which you want to increase the date_part. Date is a datetime or smalldatetime value. The DATEADD function adds an interval to a date you specify. For example, if the shipping dates of all invoices in the backorders table slip three days, you can get the new dates with this statement: SELECT DATEADD(day, 3, shipdate) FROM backorders

For reliable and cheap web hosting services please check tomcat web hosting website.

Chapter 4 QUERYING AND MODIFYING DATA 217 REVIEW BREAK Data Filtering Filtering of data is the selection of the required information, using supplied criteria, to create a resultset with only the requested data. Filtering minimizes the use of valuable bandwidth, provides for more efficient applications, and in general better suits the end user and business needs. Formatting data is presenting the information so that more value is produced in the finished product. Formatting information can be as simple as placing output in the correct sequence; however, formatting can also mean the use of highly advanced procedures for the analysis and display of data. DATA SUMMARY . Summarize data by using Transact-SQL. Summarizing information to provide results that collect data in a meaningful manner applies functions against the data that can perform calculations, make decisions, and provide answers by performing data analysis on behalf of the user. All functions are either deterministic or nondeterministic. Whether a function is deterministic or nondeterministic is called the determinism of the function. Deterministic functions always return the same result any time they are called with a specific set of input values. Nondeterministic functions may return different results each time they are called with a specific set of input values. For example, the DATEADD built-in function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE is not deterministic because it is always invoked with the same argument, yet the value it returns changes each time it is executed.

For high quality java hosting services please check tomcat web hosting website.