PHP MySql Web Hosting

,ch04.13730 Page 181 Wednesday, November 29, 2000 4:43 PM Table 4-8: MySQL-Supported Functions (continued) Function from_unixtime(unix_ timestamp) from_unixtime(unix_ timestamp,format) get_lock(str,timeout) greatest(X,Y,…) hex(N) interval(N,N1,N2,N3,…) hour(time) if(expr1,expr2,expr3) ifnull(expr1,expr2) isnull(expr) insert(str,pos,len,newstr) instr(str,substr) last_insert_id([expr]) lcase(str) lower(str) least(X,Y,…) left(str,len) length(str) octet_length(str) char_length(str) character_length(str) load_file(file_name) locate(substr,str) position(substr IN str) locate(substr,str,pos) Description Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. Returns a string representation of the unix_timestamp, formatted according to the format string. Format may contain the same specifiers as those listed in the entry for the DATE_ FORMAT( ) function. Tries to obtain a lock with a name given by the string str, with a timeout of timeout seconds. Returns 1 if the lock is obtained successfully, 0 if the attempt times out, or NULL if an error occurs. Returns the largest (maximum-valued) argument. Returns a string representation of the hexadecimal value of N, where N is a long (BIGINT ) number. This is equivalent to CONV(N,10,16). Returns NULL if N is NULL. Returns 0 if N < N1,1if N < N2, and so on. All arguments are treated as integers. It is required that N1 < N2 < N3 <... < Nn for this function to work correctly. Returns the hour for time, in the range 0 to 23. If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), then IF( ) returns expr2, else it returns expr3. IF( ) returns a numeric or string value, depending on the context in which it is used. If expr1 is not NULL, IFNULL( ) returns expr1; otherwise it returns expr2. IFNULL( ) returns a numeric or string value, depending on the context in which it is used. If expr is NULL, ISNULL( ) returns 1; otherwise it returns 0. Returns the string str. The substring begins at position pos and is 10 characters long, replaced by the string newstr. Returns the position of the first occurrence of substring substr in string str. Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. Returns the string str with all characters changed to lowercase according to the current character-set mapping (default is ISO-8859-1 Latin1). With two or more arguments, returns the smallest (minimumvalued) argument. Returns the leftmost len characters from the string str. These functions return the length of the string str. Reads the file and returns the file contents as a string. The file must be on the server, and the user must specify the full path- name to the file and have the file privilege. Returns the position of the first occurrence of substring substr in string str. Returns 0 if substr is not in str. Returns the position of the first occurrence of substring substr in string str, starting at position pos; returns 0 if substr is not in str. Functions Vendor Extensions 181

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services

,ch04.13730 Page 179 Wednesday, November 29, 2000 4:43 PM Table 4-8: MySQL-Supported Functions (continued) Function atan(X) atan2(X,Y) avg(expr) benchmark(count,expr) binary bin(N) bit_count(N) bit_and(expr) bit_or(expr) CASE value WHEN [compare-value] THEN result [WHEN [comparevalue] THEN result …] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result .. .] [ELSE result] END ceiling(X) char(N,…) coalesce(list) concat(str1,str2,…) concat_ws(separator, str1, str2,…) connection_id( ) conv(N,from_base,to_ base) cos(X) cot(X) count(DISTINCT expr,[expr…]) count(expr) curdate( ) current_date Description Returns the arctangent of X, i.e., the value whose tangent is X. Returns the arctangent of the two variables X and Y. Returns the average value of expr. Executes the expression expr count times. It may be used to time how fast MySQL processes the expression. The result value is always 0. Casts the string following it to a binary string. Returns a string representation of the binary value of N, where N is a long (BIGINT ) number. Returns the number of bits that are set in the argument N. Returns the bitwise AND of all bits in expr. The calculation is performed with 64-bit (BIGINT ) precision. Returns the bitwise OR of all bits in expr. The calculation is performed with 64-bit (BIGINT ) precision. The first version returns the result where value=comparevalue. The second version returns the result for the first condition that is true. If there is no matching result value, then the result after ELSE is returned. If there is no ELSE part, NULL is returned. Returns the smallest integer value not less than X. Interprets the arguments as integers and returns a string consisting of the characters given by the ASCII code values of those integers. NULL values are skipped. Returns first non-NULL element in the list. Returns the string that results from concatenating the arguments. Stands for CONCAT With Separator and is a special form of CONCAT( ). The first argument is the separator for the rest of the arguments. The separator and the rest of the arguments can be a string. If the separator is NULL, the result is NULL. The function skips any NULLs and empty strings after the separator argument. The separator is added between the strings to be concatenated. Returns the connection ID (thread_id ) for the connection. Every connection has its own unique ID. Converts numbers between different number bases; returns a string representation of the number N, converted from base from_base to base to_base; returns NULL if any argument is NULL. Returns the cosine of X, where X is given in radians. Returns the cotangent of X. Returns a count of the number of different values. Returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement. Returns today s date as a value in YYYY-MM-DD or YYYYMMDD format, depending on whether the function is used in a string or numeric context. Functions Vendor Extensions 179

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

,ch04.13730 Page 178 Wednesday, November 29, 2000 4:43 PM Table 4-7: Microsoft SQL Server Supported Functions (continued) Function isdate(expression) is_member({ group | role }) is_srvrolemember ( role [, login ]) isnull(check_expression, replacement_value) isnumeric(expression) left(character_ expression, integer_ expression) len(string_expression) log(float_expression) log10(float_expression) lower(character_ expression) ltrim(character_ expression) max([All | Distinct] expression) min([All | Distinct] expression) month(date) nchar(integer_ expression) newid( ) nullif(expression, expression) object_id( object ) object_name(object_id) objectproperty (id, property) Description Validates if a character string can be converted to DATETIME. Returns true or false (1 or 0) depending on whether user is a member of NT group or SQL Server role. Returns true or false (1 or 0) depending on whether user is a member of specified server role. Returns the first argument if it is not NULL; otherwise, returns the second argument. Validates if a character string can be converted to NUMERIC. Returns a portion of a character expression, starting at integer_expression from left. Returns the number of characters in the expression. Returns natural logarithm. Returns base-10 logarithm. Converts a string to lowercase. Trims leading-space characters. Finds maximum value in a column. Finds minimum value in a column. Returns month part of the date provided. Returns the unicode character with the given integer code. Creates a new unique identifier of type uniqueidentifier. Returns NULL if two specified expressions are equivalent. Returns object ID and given name. Returns object name and given ID. Returns properties of objects in the current database. MySQL-Supported Functions Table 4-8 provides an alphabetical listing of MySQL-supported functions. Table 4-8: MySQL-Supported Functions Function abs(X) acos(X) ascii(str) asin(X) Description Returns the absolute value of X. Returns the arc cosine of X, i.e., the value whose cosine is X; returns NULL if X is not in the range 1 to 1. Returns the ASCII code value of the leftmost character of the string str; returns 0 if str is the empty string; returns NULL if str is NULL. Returns the arc sine of X, i.e., the value whose sine is X; returns NULL if X is not in the range 1 to 1. 178 Chapter 4 SQL Functions

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

,ch04.13730 Page 177 Wednesday, November 29, 2000 4:43 PM Table 4-7: Microsoft SQL Server Supported Functions (continued) Function degrees(numeric_ expression) difference(character_ expression, character_ expression) exp(float_expression) floor(numeric_ expression) file_id( file_name ) file_name(file_id) filegroup_id ( filegroup_name ) filegroup_name (filegroup_id) filegroupproperty (filegroup_name, property) fileproperty (file, property) fulltextcatalog property(catalog_name, property) fulltextservice property(property) formatmessage (msg_number, param_ value [, n ]) freetexttable(table { column |*}, freetext_ string [, top_n_by_rank]) getdate( ) getansinull([ database ]) getutcdate( ) grouping(column_ name) host_id( ) host_name( ) ident_incr ( table_or_view ) ident_seed ( table_or_view ) ident_current ( table_name ) identity(data_type [, seed, increment]) As column_name index_col( table , index_id, key_id) indexproperty(table_id, index, property) Description Converts radians to degrees. Compares how two arguments sound and returns a number from 0 to 4. Higher result indicates better phonetic match. Returns exponential value. Returns largest integer less than or equal to the argument. Returns the file ID for the logical filename. Returns the logical filename for file ID. Returns filegroup ID for the logical filegroup name. Returns the logical filegroup name for filegroup ID. Returns filegroup property value for the specified property. Returns file property value for the specified property. Returns full-text catalog properties. Returns full-text service level properties. Constructs a message from an existing message in SYSMESSAGES table (similar to RAISERROR). Used for full-text search; returns a table with columns that match the meaning but don t exactly match value of freetext_ string. Returns current date and time. Returns default nullability setting for new columns. Returns Universal Time Coordinate (UTC) date. Returns 1 when the row is added by CUBE or ROLLUP; other wise, returns 0. Returns workstation ID of a given process. Returns process hostname. Returns identity-column increment value. Returns identity seed value. Returns the last identity value generated for the specified table. Used in SELECT INTO statement to insert an identity column into the destination table. Returns index column name, given table ID, index ID, and column sequential number in the index key. Returns index property (such as Fillfactor). Functions Vendor Extensions 177

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

,ch04.13730 Page 176 Wednesday, November 29, 2000 4:43 PM Table 4-7: Microsoft SQL Server Supported Functions (continued) Function checksum_agg([ALL | Distinct] expression) coalesce(expression [, n]) col_length( table , column ) col_name(table_id, column_id) contains({column | }, contains_search_ condition }) containsable(table, column, contains_ search_condition) convert(data_type [(length)], expression [, style]) cos(float_expression) cot(float_expression) count({[All | Distinct] expression]| *}) count(*) count( DISTINCT expression ) count( expression ) count_big([All | Distinct] expression) current_timestamp current_user datalength(expression) databasepropertyex(data base, property) dateadd(datepart, number, date) datediff(datepart, startdate, enddate) datename(datepart, date) datepart(datepart, date) day(date) db_id( [database_ name] ) db_name(database_id) Description Returns checksum of the values in group. Returns the first non-NULL argument from a list of arguments. Returns column length in bytes. Returns column name, given table ID and column ID. Searches columns on exact or fuzzy matches of contains_ seach_criteria. It is an elaborate function used to perform full-text searches. Refer to the vendor documentation for more information. Returns a table with exact and fuzzy matches of contains_ search_condition. It is an elaborate function used to perform full-text searches. Refer to the vendor documentation for more information. Converts data from one datatype to another. Returns cosine. Returns cotangent. Counts rows. Computes the number of rows, including those with NULL values. Calculates the number of distinct non-NULL values in a column or expression. Each group of rows with the same value of expression adds 1 to the result. Returns the number of rows with non-NULL values in a certain column or expression. Same as count except returns big integer. Returns current date and time. Returns username in the current database of the current session. Returns number of bytes in a character or binary string. Returns database option or property. Adds a number of dateparts (e.g., days) to a datetime value. Calculates difference between two datetime values expressed in certain dateparts. Returns name of a datepart (e.g., month) of a datetime argument. Returns value of a datepart (e.g., hour) of a datetime argument. Returns an integer value representing the day of the date provided as a parameter. Returns database ID and given name. Returns the database name. 176 Chapter 4 SQL Functions

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

,ch04.13730 Page 174 Wednesday, November 29, 2000 4:43 PM Examples These examples generally work on any one of the four database vendors profiled in this book. Only the second Oracle example, with a negative starting position, fails on the others (assuming, of course, that Oracle s SUBSTR is translated into SUBSTRING): /* On Oracle, counting from the left */ SELECT SUBSTR(’ABCDEFG’,3,4) FROM DUAL; -> CDEF /* On Oracle, counting from the right */ SELECT SUBSTR(’ABCDEFG’,-5,4) FROM DUAL; -> CDEF /* On MySQL */ SELECT SUBSTRING(’Be vewy, vewy quiet’,5); -> ‘wy, vewy quiet'’ /* On PostgreSQL or SQL Server */ SELECT au_lname, SUBSTRING(au_fname, 1, 1) FROM authors WHERE au_lname = ‘Carson’ -> Carson C TRIM The TRIM function removes leading spaces, trailing characters, or both from a specified character string. This function also removes other types of characters from a specified character string. The default function is to trim the specified character from both sides of the character string. If no removal string is specified, TRIM removes spaces by default. SQL99 Syntax TRIM( [ [{LEADING | TRAILING | BOTH}] [removal_string] FROM ] target_string [COLLATE collation_name]) The removal_string is the character string to be stripped out. The target _string is the character string from which characters are to be taken. If a removal_string is not specified, then TRIM strips out spaces. The COLLATE clause forces the result set of the function into another preexisting collation set. MySQL, PostgreSQL, and Oracle support the SQL99 syntax of TRIM. Microsoft SQL Server (and the other vendors for that matter) provide the functions LTRIM and RTRIM to trim off leading spaces or trailing spaces, respectively. LTRIM and RTRIM cannot be used to trim other types of characters. Examples SELECT TRIM(’ wamalamadingdong ‘); -> ‘wamalamadingdong’ 174 Chapter 4 SQL Functions

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

,ch04.13730 Page 172 Wednesday, November 29, 2000 4:43 PM format of a date conversion. Refer to the vendor documentation for more information. Following is an example: SELECT title, CONVERT(char(7), ytd_sales) FROM titles ORDER BY title GO Oracle Syntax and Variations CONVERT(’char_value’, target_char_set, source_char_set) TRANSLATE(’char_value’, ‘from_text’, ‘to_text’) Under Oracle s implementation, the CONVERT function returns the char_value in the target character set. The char_value is the string being converted, while the target_char_set is the name of the character set where the char_value is converted. Source_char_set is the name of the character set where the char_value was originally stored. Both the target and source character set can be either literals strings, variables, or columns containing the name of the character set. Note that inadequate replacement characters might be substituted when converting from or to a character set that does not support a representation of all the characters used in the conversion. Oracle supports several common character sets including US7ASCII, WE8DECDEC, WE8HP, F7DEC, WE8EBCDIC500, WE8PC850, and WE8ISO8859P1. For example: SELECT CONVERT(’Gro ‘, ‘US7ASCII’, ‘WE8HP’) FROM DUAL; ->Gross PostgreSQL Syntax and Variations TRANSLATE (character_string, from_text, to_text) PostgreSQL does not support CONVERT. PostgreSQL s implementation of the TRANSLATE function offers a large superset of functions compared to that found in the SQL99 specification. Instead, it converts any occurrence of one text string to another within another specified string. Here is an example: SELECT TRANSLATE(’12345abcde’, ‘5a’, ‘XX’); -> 1234XXbcde SELECT TRANSLATE(title, ‘Computer’, ‘PC’) FROM titles WHERE type = ‘Personal_computer’ LOWER and UPPER The functions LOWER and UPPER allow the case of a string to be altered quickly and easily, so that all the characters are lower-or uppercase, respectively. These functions are supported in all the database implementations covered in this book. 172 Chapter 4 SQL Functions

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

,ch04.13730 Page 168 Wednesday, November 29, 2000 4:43 PM BIT_LENGTH, CHAR_LENGTH, and OCTET_LENGTH The closest any of the vendors get to the BIT_LENGTH function is Oracle. Oracle supports the LENGTHB function, which returns an integer value representing the number of bytes in an expression. MySQL and PostgreSQL support CHAR_LENGTH and the SQL99 synonym CHARACTER_LENGTH( ). PostgreSQL also supports EXTRACT( ), OCTET_LENGTH( ), and POSITION( ) as per the SQL99 standard. The other two vendors each have a similar function that provides identical functionality. SQL Server provides the LEN function and Oracle provides the LENGTH function. MySQL and PostgreSQL also fully support the OCTET_LENGTH function. Example The following example determines the length of a string and a value retrieved from a column: /* On MySQL and PostgreSQL */ SELECT CHAR_LENGTH(’hello’); SELECT OCTET_LENGTH(book_title) FROM titles; /* On Microsoft SQL Server */ SELECT DATALENGTH(title) FROM titles WHERE type = ‘popular_comp’ GO /* On Oracle */ SELECT LENGTH(’HORATIO’) “Length of characters” FROM dual; EXTRACT The EXTRACT function is not supported by the database vendors, except for PostgreSQL and MySQL. Each vendor supports a separate command to accomplish the same functionality. Oracle uses the TO_CHAR function to extract a portion of a date into a character string. SQL Server uses the CONVERT function to extract a portion of a date. MySQL implementation is extended somewhat beyond the SQL99 standard. The SQL99 standard does not have a provision for returning multiple fields in the same call to EXTRACT( ) (e.g., DAY_HOUR ). The MySQL extensions try to accomplish what the combination DATE_TRUNC( ) and DATE_PART( ) do in PostgreSQL. MySQL supports the dateparts listed in Table 4-5. Table 4-5: MySQL Dateparts Type value Meaning Expected format SECOND Seconds SECONDS MINUTE Minutes MINUTES 168 Chapter 4 SQL Functions

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services

,ch04.13730 Page 167 Wednesday, November 29, 2000 4:43 PM Microsoft SQL Server supports all the built-in scalar functions. Oracle does not support the built-in scalar functions shown above; however, it supports USER as a synonym of CURRENT_USER and SYSDATE as a synonym of CURRENT_ TIMESTAMP. MySQL supports all the SQL99 built-in scalar functions, plus both of Oracle s variants. PostgreSQL supports USER, as defined in SQL99, as a synonym for CURRENT_USER. In addition, MySQL supports NOW( ) and UNIX_TIMESTAMP( ) as synonyms of the function CURRENT_TIMESTAMP. PostgreSQL supports all the SQL99 built-in scalar functions except SESSION_USER. Example The following queries retrieve the values from built-in functions. Notice that the various vendors return dates in their native formats: /* On MySQL */ SELECT CURRENT_TIMESTAMP; -> ‘2001-12-15 23:50:26′ /* On Microsoft SQL Server */ SELECT CURRENT_TIMESTAMP GO -> ‘Dec 15,2001 23:50:26′ /* On Oracle */ SELECT USER FROM dual; -> dylan Numeric Scalar Functions The list of official SQL99 numeric functions is rather small. The various vendors provide quite a large supplement of mathematical and statistical functions. MySQL supports many of these commands in its SQL99 incarnations. The other database products offer the same capabilities of numeric scalar functions through their own internally defined functions, but they do not share the same name as those declared by the SQL standard. The supported numeric functions and syntax are listed in Table 4-4. Table 4-4: SQL99 Numeric Functions Functions Function BIT_LENGTH(expression) CHAR_LENGTH(expression) EXTRACT(datetime_expression datepart FROM expression) OCTET_LENGTH(expression) POSITION(starting_string IN search_string) Usage Returns an integer value representing the number of bits in an expression. Returns an integer value representing the number of characters in an expression. Allows the datepart to be extracted (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, or TIMEZONE_MINUTE ) from an expression. Returns an integer value representing the number of octets in an expression. This value is the same as BIT_LENGTH/8. Returns an integer value representing the starting position of a string within the search string. MIN and MAX 167

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services

,ch04.13730 Page 163 Wednesday, November 29, 2000 4:43 PM CHAPTER 4 SQL Functions A function is a special type of command word in the SQL99 command set. In effect, functions are one-word commands that return a single value. The value of a function can be determined by input parameters, as with a function that averages a list of database values. But many functions do not use any type of input parameter, such as the function that returns the current system time, CURRENT_TIME. The SQL99 standard supports a number of useful functions. This chapter covers those functions, providing detailed descriptions and examples. In addition, each database vendor maintains a long list of their own internal functions that are outside of the scope of the SQL standard. Lists and descriptions are provided for each database implementation s internal functions. In addition, most database vendors support the ability to create user-defined functions (UDF). For more information on UDFs, refer to the CREATE FUNCTION command in Chapter 3, SQL Statements Command Reference. Deterministic and Nondeterministic Functions Functions can be either deterministic or nondeterministic. A deterministic function always returns the same results if given the same input values. A nondeterministic function returns different results every time it is called, even when the same input values are provided. Why is this important? It is important because of how functions may be used within views, user-defined functions, and stored procedures. The restrictions vary across implementations, but these objects sometimes allow only deterministic functions within their defining code. For example, Microsoft SQL Server allows the creation of an index on a column expression as long as the expression does not contain nondeterministic functions. Rules and restrictions vary between the vendors, so check their documentation when using functions. Functions 163

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services