Domain Name Hosting, Java Programming - Cheap Web Hosting Blog

214 Part II . SQL Essentials Figure 9-41: The ASCII and CHAR functions. Notice that when given more than one argument, the CHAR value returns the corresponding values without any separation. The LTRIM and RTRIMfunctions along with the LPAD and RPADfunctions both perform similar duties. The LTRIM and LPADfunctions operate on the left side of a string while the RTRIM and RPADfunctions operate on, you guessed it, the right side of a string. The LTRIM and RTRIMfunctions remove whitespace from a string while the LPADand RPAD functions add a specified number of characters to a string to make it a certain length. The characters to be added with LPADand RPAD can be whitespace or other characters. The total length of the string will be the length specified; any initial string longer than that length gets trimmed. (See Figure 9-42.) SELECT LTRIM( Hello World ); SELECT RTRIM( Goodbye World ); SELECT LPAD( Internet ,5, ); SELECT LPAD( Internet ,10, - ); SELECT RPAD( Another string ,16, : ); A somewhat different function for removing characters from a string is TRIM. Both the LTRIMand RTRIM functions only remove whitespace characters. However, with TRIM you can specify the character or string to remove. With TRIM you can also specify whether to remove the characters from the left side, right side, or both sides of the string. The syntax is somewhat specific for the TRIMfunction. If you include options such as BOTH, LEADING, or TRAILING, you must include the word FROM in the statement. The TRIMfunction will remove whitespace if you don t include any options such as BOTH, LEADING, TRAILING, or the string to remove. The command looks like this: TRIM ((BOTH | LEADING | TRAILING) () FROM) ; Syntax examples follow (as well as results in Figure 9-43): SELECT TRIM( Hello Rebecca ); SELECT TRIM(BOTH $ FROM $$$$Survey Says$$$$ ); SELECT TRIM(LEADING S FROM Steve Suehring );
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

Chapter 9 . SQL According to MySQL 213 Table 9-4 String functions with the SELECT statement ASCII(C) Returns the ASCII value of the given character, C. CHAR(N,N) Returns the actual character when given an ASCII number(s), N. CONCAT(S1,S2…) Concatenates (joins) the strings, S1, S2 and so on. CONCAT_WS(S,S1,S2 ..) Concatenates the strings S1, S2 and so on using separator, S. FIELD(S,S1,S2,…) Returns the number indicating the position of a string, S, as located in the field list, S1, S2, and so on. LCASE(S) Converts string, S, into lowercase. LENGTH(S) Returns the character length of string, S. LIKE Compares strings via pattern matching. LOAD_FILE(F) Loads the contents of file, F, as a string. LPAD(S,N,C) Left-pads the string, S, with the number, N, of characters, C. LTRIM(S) Trims whitespace from the left of string, S. POSITION(S in S1) Determines the first position of substring, S, in string, S1. REGEXP Compares strings with regular expression pattern matching. REPLACE(S,O,N) Replaces old string, O, with new string, N, in a given string, S. REVERSE(S) Reverses string, S. RPAD(S,N,C) Right-pads the string, S, with the number, N, of characters, C. RTRIM(S) Trims whitespace from the right of string, S. SPACE(N) Returns a string consisting of N spaces. STRCMP(S1,S2) Compares the two strings, S1 and S2. TRIM((RS) from S) Remove string, RS, from string, S (see example following this list) UCASE(S) Converts string, S, into uppercase. Out of the string functions, the ASCII and CHAR functions stand out as unique. These two functions work with strings to determine or return their ASCII value. (See Figure 9-41.) The CHARfunction can accept more than one ASCII number in the argument list. SELECT ASCII( W ); SELECT CHAR(51,65);
If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.

212 Part II . SQL Essentials The GREATEST and LEASTfunctions are similar insofar as they find the maximum or minimum value given a list of numbers, as shown in Figure 9-39. SELECT GREATEST(2,1984,5150,3); SELECT LEAST(4,87,44,62,41,.21); Figure 9-39: The GREATEST and LEAST functions in action The ROUND function can be called with or without an extra argument specifying the number of digits to include after the decimal (see Figure 9-40). If no extra argument is given, the ROUNDfunction will round to the nearest whole number. SELECT ROUND(6.79); SELECT ROUND(6.79, 1); Figure 9-40: The ROUND function can be called with or without an argument depending on the precision of rounding that you need. String functions with the SELECT statement Functions for working with strings and characters are an important part of MySQL s SELECT and WHEREstatements. As with the date and time functions and numerical functions, many string functions are used in specialized cases and applications. Table 9-4 lists many string functions used in MySQL.
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

Chapter 9 . SQL According to MySQL 211 MySQL s numerical functions also include addition, subtraction, multiplication, and division. Addition and subtraction functions can be called through the use of the plus and minus signs, multiplication uses the asterisk, and division uses the front- slash (/). For example, the statement SELECT 4 + 4 would render a result of 8. The MOD function returns the modulo or remainder from a division of numbers. This function can also be called with the percent sign (%). Beyond the functions listed above, MySQL also includes functions for cosine, tangent, arc cosine, cotangent, random, and more. Many of these functions are used only in specialized applications. Cross-The additional numerical functions for use with MySQL are detailed in Appendix A. Reference The numerical functions are really straightforward in their use. Most accept a number as an argument. Some interesting functions include the BIN, HEX, and FORMAT functions. The BIN and HEXfunctions convert a number between formats binary and hexadecimal respectively. The format function is useful when you have extra decimal places and want only to use a certain number of decimal places in your application. Syntax examples and results follow (see Figure 9-38). Notice that I sneaked in a nested function, PI, in the FORMAT function call. SELECT BIN(5); SELECT BIN(256); SELECT HEX(134); SELECT FORMAT(PI(), 2); Figure 9-38: Some of MySQL s numerical functions in action
From our experience, we can recommend PHP Web Hosting services, if you need affordable webhost to host and run your web application.

210 Part II . SQL Essentials Table 9-3 Numerical functions with the SELECT statement ABS(N) Returns the absolute value of a number, N. Figure 9-37: The DATE_FORMAT function allows customizing of date and time output to fit your application. Numerical functions with SELECT As is the case with dates and times, MySQL also includes a rich feature set of numerical functions. Table 9-3 lists some of those functions. xBIN(N) Returns the binary value for the given number, N. xFORMAT(N,D) Formats the number, N, to the given decimal place, D. xGREATEST(N1,N2, …) Provides the highest or greatest number in the given set, N1, N2, and so on. xHEX(D) Gives the hexadecimal value for a given decimal number, D. xLEAST(N1, N2, …) Returns the smallest or least number in the list N1, N2, and so on. xMOD(N1, N2) Returns the modulo value of the two numbers, N1 and N2. xPI() Returns the value of Pi. POW(N1, N2) Raises a number, N1 to the power of another number, N2. xROUND(N,(D)) Rounds a number, N, to the optional decimal places, D. SIGN(N) Determines whether a value is negative, positive, or zero.
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

Chapter 9 . SQL According to MySQL 209 Argument Definition %h One-digit numeric hour starting with 0 in 12-hour format. %I Two-digit numeric hour starting with 01 in 12-hour format. %I Two-digit numeric minute starting with 00. %j Three-digit day of year starting with 001. %k One-digit numeric hour starting with 0 in 24-hour format. %l One-digit numeric hour starting with 1 in 12-hour format. %M Long format month name, January, February, and so on. %m Two-digit numeric month starting with 01. %p AM or PM designation based on the time. %r Time in 12-hour format including AM or PM. %S Two-digit numeric seconds. %s Two-digit numeric seconds, same as %S. %T Time in 24-hour format. %U One-digit numeric week where Sunday is first day of week. %u One-digit numeric week where Monday is first day of week. %W Long format weekday name such as Monday, Tuesday. %w Numeric day of week where 0 is Sunday. %Y Four-digit numeric year. %y Two-digit numeric year. Don t let the options for the DATE_FORMATfunction overwhelm you. Some syntax examples for the DATE_FORMAT function are as follows: SELECT DATE_FORMAT(NOW(), %T ); SELECT DATE_FORMAT( 1998-03-16 , %W, %M %D, %Y ); The results from the syntax examples for the DATE_FORMAT function are shown in Figure 9-37.
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

208 Part II . SQL Essentials Figure 9-36: Time and second based conversion functions in MySQL Out of the functions, the DATE_FORMAT statement stands out as unique. This function takes a date and then determines how you would like the date result formatted. Quite a few options exist. Table 9-2 lists the various formatting arguments that you can provide to the DATE_FORMAT function. Table 9-2 Arguments for use with the DATE_FORMAT function Argument Definition %% A literal percent sign. %a Abbreviated weekday names such as Sun, Mon, and so on. %b Abbreviated month names, Jan, Feb, and so on. %c The month number, where 1 is January. %D Numeric day of month with suffix such as 1st, 2nd, and so on. %d Two-digit numeric day of month starting with 00, 01, and so on. %e One-digit numeric day of month starting with 0. %H Two-digit numeric hour starting with 00 in 24-hour format.
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.

Chapter 9 . SQL According to MySQL 207 The FROM_DAYS and TO_DAYSfunctions are similar insofar as they tell you the number of days to or from a certain date (see Figure 9-35). For example, to determine the date in a certain number of days, use the FROM_DAYS with the number as the argument. To determine the number of days from year 0 to a certain date, use the TO_DAYS function like this: SELECT FROM_DAYS( 732000 ); SELECT TO_DAYS( 2003-01-26 ); Note The functions work with numbers of days since year zero; therefore the values you use will be quite large. Figure 9-35: The FROM_DAYS and TO_DAYS functions in action. Note the large values used as both of the functions work with the number of days since year 0. Some date and time functions convert dates and times from one format to another. These functions include FROM_UNIXTIME, SEC_TO_TIME, TIME_TO_SEC, and UNIX_TIMESTAMP. Using these functions in conjunction with other time and date functions, you can transform the dates and times so they are easier to understand. In Unix/Linux, many are based upon the number of seconds. If you have a Unix timestamp and need to convert that to a more easily readable date, use the FROM_UNIXTIME function. The UNIX_TIMESTAMPfunction gives you the value of the Unix time in seconds. You could nest the two functions to retrieve the current date, but why not just use the CURDATEfunction? The SEC_TO_TIMEand TIME_TO_SEC functions work with times though not necessarily related to times you d see on a clock. Thus you can use the SEC_TO_TIME function to determine how many hours a session was active. Syntax examples follow (with results in Figure 9-36): SELECT UNIX_TIMESTAMP(); SELECT FROM_UNIXTIME( 1000010234 ); SELECT SEC_TO_TIME( 124024 ); SELECT TIME_TO_SEC( 51:50:00 );
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

206 Part II . SQL Essentials Figure 9-33: Examples of various date addition and subtraction functions with MySQL The functions PERIOD_ADDand PERIOD_DIFF also add and subtract dates, though each works with months only. With PERIOD_ADD, you add a number of months to a give date. With PERIOD_DIFF you can determine the number of months between two pseudo-date values. The syntax looks like these examples (results appear in Figure 9-34): SELECT PERIOD_ADD(200205, 3); SELECT PERIOD_DIFF(200208,200206); Figure 9-34: Using MySQL s PERIOD_ADD and PERIOD_DIFF functions to work with months
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

Chapter 9 . SQL According to MySQL 205 Figure 9-32: Other MySQL date determination functions for week, month, quarter, and year As with the functions that parse a date and return information, other functions parse a given time and return information. These include HOUR, MINUTE, and SECOND. As with the functions for working with days and weeks, the time-based functions also accept a given date/time argument and return information based on that argument. Some MySQL date and time functions add or subtract dates and time. For example, DATE_ADD and DATE_SUBperform addition and subtraction of dates, respectively. Both functions require two arguments, a date and an interval to add or subtract. As stated previously, you can nest functions in statements; this might be a place where you would nest the NOW() function in the DATE_ADDfunction to quickly determine the current date. Both the DATE_ADDand DATE_SUB functions have synonyms: ADDDATE and SUBDATE, respectively. In addition, you can also use a plus sign (+) and minus sign ( ) as substitutes for the DATE_ADD and DATE_SUBfunctions. The functions accept any valid time interval such as year, month, day, hour, and even second. Results from the following examples are shown in Figure 9-33. SELECT DATE_ADD(NOW(), INTERVAL 745 day); SELECT DATE_ADD( 1971-03-12 , INTERVAL 3 second); SELECT 1991-03-16 + 10 year; SELECT DATE_SUB( 1997-01-01 , INTERVAL 431 month);
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.