,ch04.13730 Page 193 Wednesday, November 29, 2000 4:43 PM Table 4-10: PostgreSQL Supported Functions (continued) Function position(string in string) pow(float8,float8) popen(path) reltime(interval) radians(float8) radius(circle) round(float8) rpad(text,int,text) rtrim(text,text) sin(float8) sqrt(float8) substring(string [from int] [for int]) substr(text,int[,int]) tan(float8) text(char) text(varchar) textpos(text,text) timestamp(date) timestamp(date,time) to_char(timestamp, text) to_char(int, text) to_char(float, text) to_char(numeric, text) to_date(text, text) to_number(text, text) to_timestamp(text, text) translate(text,from,to) trim([leading|trailing| both] [string] from string) trunc(float8) upper(text) varchar(char) varchar(text) width(box) Description Returns location of specified substring Raises a number to the specified exponent Converts path to open path Converts to reltime Converts degrees to radians Returns radius of circle Rounds to nearest integer Converts right pad string to specified length Converts right trim characters from text Returns sine Returns square root Extracts specified substring Extracts specified substring Returns tangent Converts char to text type Converts varchar to text type Locates specified substring Converts to timestamp Converts to timestamp Converts timestamp to string Converts int4/int8 to string Converts float4/float8 to string Converts numeric to string Converts string to date Converts string to numeric Converts string to timestamp Converts character in string Trims characters from string Truncates (towards zero) Converts text to uppercase Converts char to varchar type Converts text to varchar type Returns horizontal size Functions Vendor Extensions 193
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 191 Wednesday, November 29, 2000 4:43 PM Table 4-9: Oracle-Supported Functions (continued) Function var_samp(expression) over (analytics) variance([DISTINCT] expression) over (analytics) vsize(expression) Description Returns the sample variance of a set of numbers after discarding the nulls in the expression number set. Analytic functions are covered in the vendor documentation. Returns variance of expression calculated as follows: 0 if the number of rows in expression = 1 VAR_SAMP if the number of rows in expression > 1 Returns the number of bytes in the internal representation of expression. When expression is null, it returns null. PostgreSQL Supported Functions Table 4-10 lists the functions specific to PostgreSQL. Table 4-10: PostgreSQL Supported Functions Function abstime(timestamp) abs(float8) acos(float8) age(timestamp) age(timestamp, timestamp) area(object) asin(float8) atan(float8) atan2(float8,float8) box(box,box) box(circle) box(point,point) box(polygon) broadcast(cidr) broadcast(inet) CASE WHEN expr THEN expr […] ELSE expr END cbrt(float8) center(object) char(text) char(varchar) char_length(string) character_length(string) circle(box) circle(point,float8) COALESCE(list) cos(float8) cot(float8) Description Converts to abstime Returns absolute value Returns arccosine Preserves months and years Preserves months and years Returns area of item Returns arcsine Returns arctangent Returns arctangent Returns intersection box Converts circle to box Returns points to box Converts polygon to box Constructs broadcast address as text Constructs broadcast address as text Returns expression for first true WHEN clause Returns cube root Returns center of item Converts text to char type Converts varchar to char type Returns length of string Returns length of string Converts to circle Converts point to circle Returns first non-NULL value in list Returns cosine Returns cotangent Functions Vendor Extensions 191
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 192 Wednesday, November 29, 2000 4:43 PM Table 4-10: PostgreSQL Supported Functions (continued) Function date_ part(text,timestamp) date_part(text,interval) date_ trunc(text,timestamp) degrees(float8) diameter(circle) exp(float8) float(int) float4(int) height(box) host(inet) initcap(text) interval(reltime) integer(float) isclosed(path) isopen(path) isfinite(timestamp) isfinite(interval) length(object) ln(float8) log(float8) lower(string) lseg(box) lseg(point,point) lpad(text,int,text) ltrim(text,text) masklen(cidr) masklen(inet) netmask(inet) npoint(path) NULLIF(input,value) octet_length(string) path(polygon) pclose(path) pi( ) polygon(box) polygon(circle) polygon(npts,circle) polygon(path) point(circle) point(lseg,lseg) point(polygon) Description Returns portion of date Returns portion of time Truncates date Converts radians to degrees Returns diameter of circle Raises e to the specified exponent Converts integer to floating point Converts integer to floating point Returns vertical size of box Extracts host address as text Converts first letter of each word to uppercase Converts to interval Converts floating point to integer Returns a closed path Returns an open path Returns a finite time Returns a finite time Returns length of item Returns natural logarithm Returns base-10 logarithm Converts string to lowercase Converts box diagonal to lseg Converts points to lseg Returns left-pad string to specified length Returns left-trim characters from text Calculates netmask length Calculates netmask length Constructs netmask as text Returns number of points Returns NULL if input = value, else returns input Returns storage length of string Converts polygon to path Converts path to closed Returns fundamental constant Returns 12-point polygon Returns 12-point polygon Returns npts polygon Converts path to polygon Returns center Returns intersection Returns center 192 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 190 Wednesday, November 29, 2000 4:43 PM Table 4-9: Oracle-Supported Functions (continued) Function tanh(number) to_char (date [, format [, nls_parameter ]]) to_char (number [, format [, nls_ parameter ]]) to_date(string [, format [, nls_parameter ]]) to_lob(long_column) to_multi_byte(string) to_number(string [, format [, nls_ parameter ]]) to_single_byte(string) translate( char_value , from_text , to_text ) translate (text USING [CHAR_CS | NCHAR_CS] ) trim({[LEADING | TRAILING | BOTH] trim_ char | trim_char } FROM trim_source} ) trunc (base [, number]) trunc (date [, format]) uid upper(string) user userenv(option) value(table_alias) var_pop(expression) over (analytics) Description Returns the hyperbolic tangent of number Converts date to a VARCHAR2 in the format specified by the date format format. When fmt is omitted, date is converted to the default date format. The nls_parameter option offers additional control over formatting options. Converts number to a VARCHAR2 in the format specified by the number format format. When fmt is omitted, number is converted to a string long enough to hold the number. The nls_parameter option offers additional control over formatting options. Converts string (in CHAR or VARCHAR2) to a DATE datatype. The nls_parameter option offers additional control over formatting options. Usable only by LONG or LONG RAW expressions, it converts LONG or LONG RAW values in the column long_column to LOB values. It is usable only in the SELECT list of a subquery in an INSERT statement. Returns string with all of its single-byte characters converted to their corresponding multi-byte characters. Converts a numeric string (of CHAR or VARCHAR2 datatype) to a value of a NUMBER datatype in the format specified by the optional format model format. The nls_parameter option offers additional control over formatting options. Returns string with all of its multi-byte characters converted to their corresponding single-byte characters. Returns char_value with all occurrences of each character in from_text replaced by its corresponding character in to_text; refer to the section CONVERT and TRANSLATE earlier in this chapter for more information on TRANSLATE. Converts text into the character set specified for conversions between the database character set or the national character set. Enables leading or trailing characters (or both) to be trimmed from a character string. Returns base truncated to number decimal places. When number is omitted, base is truncated to 0 places. Number can be negative to truncate (make zero) number digits left of the decimal point. Returns date with any time data truncated to the unit specified by format. When format is omitted, date is truncated to the nearest whole day. Returns an integer that uniquely identifies the session user who logged on. No parameters are needed. Returns string with all letters in uppercase. Returns the name of the session user who logged on in VARCHAR2. Returns information about the current session in VARCHAR2. Takes as a table alias associated with a row in an object table and returns object instances stored within the object table. Returns the population variance of a set of numbers after discarding the nulls in the expression number set. Analytic functions are covered in the vendor documentation. 190 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 188 Wednesday, November 29, 2000 4:43 PM Table 4-9: Oracle-Supported Functions (continued) Function ntile(expression) over ( query_partition ORDER BY ) numtodsinterval (number, string ) numtoyminterval (number, string ) nvl(expression1, expression2) nvl2(expression1, expression2, expression3) percent_rank( ) over ( query_partition ORDER BY ) power(number, power) rank (value_expression) over ( query_partition ORDER BY ) ratio_to_report (value_exprs) over ( query_partition) rawtohex(raw) ref(table_alias) reftohex(expression) regr_ xxx(expression1, expression2) over (analytics) Description Divides an ordered data set into a number of buckets numbered 1 to expression and assigns the appropriate bucket number to each row. Converts number to an INTERVAL DAY TO SECOND literal, where number is a number or an expression resolving to a number, such as a numeric datatype column. Converts number to an INTERVAL DAY TO MONTH literal, where number is a number or an expression resolving to a number, such as a numeric datatype column. If expression1 is null, expression2 is returned in the place of a null value. Otherwise, expression1 is returned. The expressions may be any datatype. Similar to NLV, except that if expression1 is not null, expression2 is returned. If expression1 is null, expression3 is returned. The expressions may be any datatype, except LONG. Similar to the CUME_DIST analytical function. Rather than return the cumulative distribution, it returns the percentage rank of a row compared to the others in its result set. Refer to the vendor documentation for more assistance. Returns number raised to the nth power. The base and the exponent can be any numbers, but if number is negative, power must be an integer. Computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_expression in the ORDER_BY_clause. Computes the ratio of a value to the sum of a set of values. If values_expr is null, the ratio-to-report value also is null. Converts a raw value to a string (character datatype) of its hexadecimal equivalent. REF takes a table alias associated with a row from a table or view. A special reference value is returned for the object instance that is bound to the variable or row. Converts argument expression to a character value containing its hexadecimal equivalent. Linear regression functions fit an ordinary-least-squares regression line to a set of number pairs where expression1 is the dependent variable and expression2 is the independent variable. The linear regression functions are: REGR_SLOPE: returns the slope of the line REGR_INTERCEPT: returns the y-intercept of the regression line REGR_COUNT: returns the number of non-null pairs fitting the regression line REGR_R2: returns the coefficient of determination for the regression REGR_AVGX: returns the average of the independent variable REGR_AVGY: returns the average of the dependent variable REGR_SXX: calculates REGR_COUNT(exp1, exp2) * VAR_POP(exp2) REGR_SYY: calculates REGR_COUNT(exp1, exp2) * VAR_POP(exp1) REGR_SXY: calculates REGR_COUNT(exp1, exp2) * COVAR_POP(exp1, exp2) These can be used as aggregate or analytic functions. 188 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 189 Wednesday, November 29, 2000 4:43 PM Table 4-9: Oracle-Supported Functions (continued) Function replace(string, search_ string [,replacement_ string]) round (number, decimal) round (date[, format]) row_number ( ) over ( query_partition ORDER BY ) rowidtochar(rowid) rpad(string1, number [, string2]) rtrim(string[,set]) sign(number) sin(number) sinh(number) soundex(string) sqrt(number) stddev( [DISTINCT] expression) over (analytics) stdev_pop(expression) over (analytics) seddev_samp(expression) over (analytics) substr(extraction_string [FROM starting_position] [FOR length]) substrb(extraction_string [FROM starting_position] [FOR length]) sum([DISTINCT] expression) over (analytics) sys_context ( namespace , attribute [,length]) sys_guid( ) sysdate tan(number) Description Returns string with every occurrence of search_string replaced with replacement_string. Returns number rounded to decimal places right of the decimal point. When decimal is omitted, number is rounded to 0 places. Note that decimal, an integer, can be negative to round off digits left of the decimal point. Returns the date rounded to the unit specified by the format model format. When format is omitted, date is rounded to the nearest day. Assigns a unique number to each row where it is applied in the ordered sequence of rows specified by the ORDER_BY_ clause, beginning with 1. Converts a rowid value to VARCHAR2 datatype, 18 characters long. Returns string1, right-padded to length number with the value of string2, repeated as needed. String2 defaults to a single blank. Returns string, with all the rightmost characters that appear in set removed; set defaults to a single blank. When number < 0, returns 1. When number = 0, returns 0. When number > 0, returns 1. Returns the sine of number as an angle expressed in radians. Returns the hyperbolic sine of number. Returns a character string containing the phonetic representation of string. This function allows words that are spelled differently but sound alike in English to be compared for equality. Returns square root of number, a nonnegative number. Returns sample standard deviation of a set of numbers shown as expression. Computes the population standard deviation and returns the square root of the population variance. Computes the cumulative sample standard deviation and returns the square root of the sample variance. Refer to the earlier section on SUBSTR. SUBSTRB is the same as SUBSTR, except that the arguments m starting_position and length are expressed in bytes, rather than in characters. Returns sum of values of expr; refer to vendor documentation for assistance with analytics and the OVER subclause. Returns the value of attribute associated with the context namespace, usable in both SQL and PL/SQL statements. Generates and returns a globally unique identifier (RAW value) made up of 16 bytes. Returns the current date and time, requiring no arguments. Returns the tangent of number as an angle expressed in radians. Functions Vendor Extensions 189
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 187 Wednesday, November 29, 2000 4:43 PM Table 4-9: Oracle-Supported Functions (continued) Function log(base_number, number) lower(string) lpad(string1, number [,string2]) ltrim(string[, set]) make_ref({table_name | view_name} , key [, n]) max([DISTINCT] expression) over (analytics) min([DISTINCT] expression) over (analytics) mod(dividend, divider) months_between (date1, date2) new_time(date, time_ zone1, time_zone2) next_day(date, string) nls_charset_decl_ len(bytecnt, csid) nls_charset_id(text) nls_charset_ name(number) nls_initcap(string [, nlsparameter ]) nls_lower(string, [, nlsparameter ]) nlssort(string [, nlsparameter ]) nls_upper string [, nlsparameter ]) Description Returns the logarithm of any base_number of number. Returns string in the same datatype as it was supplied with all characters lowercase. Returns string1, left-padded to length number using characters in string2; string2 defaults to a single blank. Removes all characters in set from the left of string. Set defaults to a single blank. Creates a reference (REF ) to a row of an object view or a row in an object table whose object identifier is primary key-based. Returns maximum value of expression. It can be used as an aggregate or analytic function (analytic functions are beyond the scope of this text). Returns minimum value of expression. It can be used as an aggregate or analytic function (analytic functions are beyond the scope of this text). Returns remainder of dividend divided by divider; returns the dividend if divider is 0. Returns number of months between dates date1 and date2. When date1 is later than date2, the result is positive. If it is earlier, the result is negative. Returns the date and time in time_zone2 when date and time in time_zone1 are date. Time_zones 1 and 2 may be any of these text strings: AST, ADT: Atlantic Standard or Daylight Time BST, BDT: Bering Standard or Daylight Time CST, CDT: Central Standard or Daylight Time EST, EDT: Eastern Standard or Daylight Time GMT: Greenwich Mean Time HST, HDT: Alaska-Hawaii Standard Time or Daylight Time MST, MDT: Mountain Standard or Daylight Time NST: Newfoundland Standard Time PST, PDT: Pacific Standard or Daylight Time YST, YDT: Yukon Standard or Daylight Time Returns the date of the first weekday named by string that is later than date. The argument string must be either the full name or the abbreviation of a day of the week in the date language of the session. Returns the declaration width (bytecnt)ofan NCHAR column using the character set ID (csid ) of the column. Returns the NLS character set ID number corresponding to text. Returns the VARCHAR2 name for the NLS character set corresponding to the ID number. Returns string with the first letter of each word in uppercase and all other letters in lowercase. The nlsparameter offers special linguistic sorting features. Returns string with all letters lowercase. The nlsparameter offers special linguistic sorting features. Returns the string of bytes used to sort string. The nlsparameter offers special linguistic sorting features. Returns string with all letters uppercase. The nlsparameter offers special linguistic sorting features. Functions Vendor Extensions 187
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 184 Wednesday, November 29, 2000 4:43 PM Table 4-8: MySQL-Supported Functions (continued) Function substring_index (str,delim,count) substring(str,pos) substring(str FROM pos) sum(expr) tan(X). time_format (time,format) time_to_sec(time) to_days(date) trim([[BOTH | LEADING | TRAILING] [remstr] FROM] str) truncate(X,D) ucase(str) upper(str) unix_timestamp( ) unix_timestamp(date) user( ) system_user( ) session_user( ) version( ) week(date) week(date,first) weekday(date) year(date) yearweek(date) yearweek(date,first) Description Returns the substring from string str after count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. Returns a substring from string str starting at position pos. Returns the sum of expr. Note that if the return set has no rows, it returns NULL. Returns the tangent of X, where X is given in radians. This is used like DATE_FORMAT( ), but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0. Returns the time argument, converted to seconds. Given a date, returns a daynumber (the number of days since year 0). Returns the string str with all remstr prefixes and/or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING are given, BOTH is assumed. If remstr is not specified, spaces are removed. Returns the number X, truncated to D decimals. If D is 0, the result has no decimal point or fractional part. Returns the string str with all characters changed to uppercase according to the current character set mapping (default is ISO-8859-1 Latin1). If called with no argument, returns a Unix timestamp (seconds since 1970-01-01 00:00:00 GMT). If UNIX_ TIMESTAMP( ) is called with a date argument, it returns the value of the argument as seconds since 1970-01-01 00:00:00 GMT. These functions return the current MySQL username. Returns a string indicating the MySQL server version. With a single argument, returns the week for date, in the range 0 to 53. (The beginning of a week 53 is possible during some years.) The two-argument form of WEEK( ) allows the user to specify whether the week starts on Sunday (0) or Monday (1). Returns the weekday index for date (0 = Monday, 1 = Tuesday, . . . 6 = Sunday). Returns the year for date, in the range 1000 to 9999. Returns year and week for a date. The second argument works exactly like the second argument to WEEK( ). Note that the year may be different from the year in the date argument for the first and the last week of the year. Oracle SQL Supported Functions Table 4-9 provides an alphabetical listing of the SQL functions specific to Oracle. 184 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 185 Wednesday, November 29, 2000 4:43 PM Table 4-9: Oracle-Supported Functions Function abs(number) Returns the absolute value of number. acos(number) Returns the arc cosine of number ranging from 1 to 1. The result ranges from 0 to p and is expressed in radians. add_months(date, int) Returns the date date plus int months. ascii(string) Returns the decimal value in the database character set of the first character of string; returns an ASCII value when the database character set is 7-bit ASCII; returns EBCDIC values if the database character set is EBCDIC Code Page 500. asin(number) Returns the arc sine of number ranging from 1 to 1. The resulting value ranges from -p/2 to p /2 and is expressed in radians. atan(number) Returns the arctangent of any number. The resulting value ranges from -p /2 to p/2 and is expressed in radians. atan2(number,nbr) Returns the arctangent of number and nbr. The values for number and nbr are not restricted, but the results range from -p to p and are expressed in radians. avg([DISTINCT] Returns the average value of expr. It can be used as an expression) over aggregate or analytic function (analytic functions are beyond (analytics) the scope of this text). bfilename( directory , file Returns a BFILE locator associated with a physical LOB binary name ) filename on the server s filesystem in directory. ceil(number) Returns smallest integer greater than or equal to number. chartorowid(char) Converts a value from a character datatype (CHAR or VARCHAR2 datatype) to ROWID datatype. chr(number [USING Returns the character having the binary equivalent to number NCHAR_CS]) in either the database character set (if USING NCHAR_CS is not included) or the national character set (if USING NCHAR_ CS is included). concat(string1, string2) Returns string1 concatenated with string2. It is equivalent to the concatenation operator (||). convert(char_value, Converts a character string from one character set to another; target_char_set, source_ returns the char_value in the target_char_set after converting char_set) char_value from the source_char_set. corr(expression1, Returns the correlation coefficient of a set of numbered pairs expression2) over (expressions 1 and 2). It can be used as an aggregate or (analytics) analytic function (analytic functions are beyond the scope of this text). cos(number) Returns the cosine of number as an angle expressed in radians. cosh(number) Returns the hyperbolic cosine of number. count Returns the number of rows in the query; refer to the earlier section on COUNT for more information. covar_pop(expression1, Returns the population covariance of a set of number pairs expression2) over (expressions 1 and 2). It can be used as an aggregate or (analytics) analytic function (analytic functions are beyond the scope of this text). covar_samp(expression1, Returns the sample covariance of a set of number pairs expression2) (expressions 1 and 2). It can be used as an aggregate or over(analytics) analytic function (analytic functions are beyond the scope of this text). cume_dist( ) ( [OVER The cumulative distribution function computes the relative (query)] ORDER BY ) position of a specified value in a group of values. Description Functions Vendor Extensions 185
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 186 Wednesday, November 29, 2000 4:43 PM Table 4-9: Oracle-Supported Functions (continued) Function decode(expr search , result [,. n] [,default]) dense_rank( ) ( [OVER (query)] ORDER BY ) deref(expression) dump(expression [,return_format [, starting_at [,length]]] ) empth[B | C]lob( ) exp(number) first_value( expression) over (analytics) floor(number) greatest(expression [, n]) grouping(expression) hextoraw(string) initcap(string) instr(string1, string2, start_at, occurrence) instrb(string1, string2, [start_a[t, occurrence]]) lag(expression [,offset][,default]) over(analytics) last_day(date) last_value(expression) over (analytics) lead(expression [,offset][,default]) over(analytics) least(expression [, n]) length(string) lengthb(string) ln(number) Description Compares expr to the search value; if expr is equal to a search, returns the result. Without a match, DECODE returns default, or NULL if default is omitted. Refer to Oracle documentation for more details. Computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_ exprs in the ORDER_BY_clause. Returns the object reference of expression, where expression must return a REF to an object. Returns a VARCHAR2 value containing a datatype code, length in bytes, and internal representation of expression. The resulting value is returned in the format of return_format. Returns an empty LOB locator that can be used to initialize a LOB variable. It can also be used to initialize a LOB column or attribute to empty in an INSERT or UPDATE statement. Returns E raised to the numbered power, where E = 2.71828183. Returns the first value in an ordered set of values. Returns largest integer equal to or less than number. Returns the greatest of the list of expressions. All expressions after the first are implicitly converted to the datatype of the first expression before the comparison. Distinguishes null cause by a super-aggregation in GROUP BY extension from an actual null value. Converts string containing hexadecimal digits into a raw value. Returns string, with the first letter of each word in uppercase and all other letters in lowercase. Searches one character string for another character string. INSRT search char1 with a starting position of start_at (an integer) looking for the numeric occurrence within string2. Returns the position of the character in string1 that is the first character of this occurrence. The same as INSTR, except that start_at and the return value are expressed in bytes instead of characters. Provides access to more than one row of a table at the same time without a self join; refer to the vendor documentation for more information. Returns the date of the last day of the month that contains date. Returns the last value in an ordered set of values; refer to the vendor documentation for more information. Provides access to more than one row of a table at the same time without a self join. Analytic functions are beyond the scope of this text. Returns the least of the list of expressions. Returns the integer length of string, or null if string is null. Returns the length of char in bytes; otherwise, the same as LENGTH. Returns the natural logarithm of number, where the number is greater than 0. 186 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