Chapter 7. Scalar Functions and Aggregate Functions

Index:

  1. Mathematical Functions
  2. Trigonometric Functions
  3. String Functions
  4. Date/Time Functions
  5. Boolean Functions
  6. System Functions
  7. Conversion Functions
  8. Security Functions
  9. Sequence Functions
  10. Miscellaneous Functions
  11. Aggregate Functions

Mathematical Functions

    1. ABS(x): the absolute value
    2. CEIL(x), CEILING(x): the smallest integer that is not less than x
    3. DEGREES(x): converts radians to degrees
    4. EXP(x): exponential, e(2.718...) raised to the power of x
    5. FLOOR(x): the largest integer not greater than argument x
    6. INT(x) : truncates x to nearest integer
    7. LOG(x), LN(x): the natural logarithm
    8. LOG(b,x): returns the logarithm of X for an arbitary base B
    9. LOG10(x): the base 10 logarithm
    10. LOG2(X): the base 2 logarithm
    11. LN(x): the natural logarithm
    12. MOD(y, x): the remainder of y/x, you can use y%x too.
    13. PI(): ¦Ð constant, 3.14159265358979323846.
    14. POW(x, y), POWER(x, y): x raised to the power of y
    15. RADIANS(x): converts degrees to radians
    16. RAND([seed]): a random value between 0.0 and 1.0
    17. ROUND(x [,y]): rounds x to nearest integer without y, or round x to y digits after the decimal point.
    18. SIGN(x): returns -1 if x is smaller than 0, 0 if x==0 and 1 if x is bigger than 0.
    19. SQRT(x): the square root
    20. TRUNC(x[,y]), TRUNCATE(x[,y]): truncates x to nearest integer without y, truncates x to y digits after the decimal point

Trigonometric Functions

    1. ACOS(x): the inverse cosine of an angle
    2. ASIN(x): the inverse sine of an angle
    3. ATAN(x): the inverse tangent of an angle
    4. ATAN2(x, y): the inverse tangent of x/y
    5. COS(x): the cosine of an angle
    6. COT(x): the cotangent of an angle
    7. SIN(x): the sine of an angle
    8. TAN(x): the tangent of an angle

String Functions

    1. ALLTRIM(string1): removes all leading and trailing blanks in string1
    2. ASC(string1), ASCII(string1): the ASCII code of the leftmost character of the argument
    3. AT(cSearchExpression, cExpressionSearched [, nOccurrence]): returns the beginning numeric position of the first occurrence of a character expression or memo field within another character expression or memo field, counting from the leftmost character. If the character expression isn't found, AT( ) returns 0.
    4. BIN(number1): returns a string representation of the binary value of number1, where number1 is a integer(TINYINT, SMALLINT, INT, or BIGINT) number. Returns NULL if N is NULL.
    5. BIT_LENGTH(string1): the length of the string str in bits
    6. CHAR_LENGTH(string1), CHARACTER_LENGTH(string1): the number of characters in string1
    7. CHAR(integer), CHR(integer): a character with the given ASCII code
    8. CHAR(integer1,...): interprets the arguments as integers and returns a string consisting of the characters given by the unicode values of those integers. NULL values are skipped.
    9. CHRTRAN(cSearchedExpression, cSearchExpression, cReplacementExpression): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression. CHRTRAN( ) translates the character expression cSearchedExpression using the translation expressions cSearchExpression and cReplacementExpression and returns the resulting character string. If a character in cSearchExpression is found in cSearchedExpression, the character in cSearchedExpression is replaced by a character from cReplacementExpression that's in the same position in cReplacementExpression as the respective character in cSearchExpression. If cReplacementExpression has fewer characters than cSearchExpression, the additional characters in cSearchExpression are deleted from cSearchedExpression. If cReplacementExpression has more characters than cSearchExpression, the additional characters in cReplacementExpression are ignored.
    10. CONCAT(string1, string2): string concatenation, you can use string1+string2 too.
    11. CONCAT(string1, string2,...): returns the string that results from concatenating the arguments. NULL values are skipped.
    12. CONCAT_WS(separator,string1, string2,...): returns the string that results from concatenating the arguments. The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. If the separator is NULL, the result is NULL. The function skips any NULL values after the separator argument.
    13. CONV(number1,base): returns a string representation of the first argument in the radix specified by the second argument. The minimum base is 2 and the maximum base is 36.
    14. DIFFERENCE(string1, string2): the difference between the sound of string1 and string2
    15. HEX(number1): returns a string representation of the hexadecimal value of number1, where number1 is a integer(TINYINT, SMALLINT, INT, or BIGINT) number. Returns NULL if N is NULL.
    16. INITCAP(string1): converts first letter of each word (whitespace-separated) to upper case
    17. INSERT(string1, start1, length1, string2): a string where length1 number of characters beginning at start1 has been replaced by string2
    18. INSTR(string1, string2 [,start1]): the first index (>0:left location, 0:not found) where string2 is found in string1, starting at start1
    19. LCASE(string1): converts string1 to lower case
    20. LEFT(string1, count1): the leftmost count1 of characters of string1
    21. LENGTH(string1), LEN(string1): the number of characters in string1
    22. LOCATE(string1, string2 [,start1]): the first index (>0:left location, 0:not found) where string1 is found in string2, starting at start1
    23. LOWER(string1): converts string1 to lower case
    24. LPAD(string1, length1 [, cPadCharacter]): returns a string from an expression, padded with character(a space by default) to a specified length on the left. If the string is already longer than length then it is truncated (on the right).
    25. LTRIM(string1): removes all leading blanks in string1
    26. MID(string1 FROM start1 [FOR length1]), MID(string1, start1 [,length1]): extracts the substring starting at start1 with length length1. MID is a synonym for SUBSTRING.
    27. OCT(number1): returns a string representation of the octal value of number1, where number1 is a integer(TINYINT, SMALLINT, INT, or BIGINT) number. Returns NULL if N is NULL.
    28. OCTET_LENGTH(string1): the number of octets (8-bit bytes) needed to represent the string1.
    29. PADC(string1, length1 [, cPadCharacter]): returns a string from an expression, padded with character(a space by default) to a specified length on both sides. If the string is already longer than length then it is truncated (on the right).
    30. PADL(string1, length1 [, cPadCharacter]): returns a string from an expression, padded with character(a space by default) to a specified length on the left. If the string is already longer than length then it is truncated (on the right).
    31. PADR(string1, length1 [, cPadCharacter]): returns a string from an expression, padded with character(a space by default) to a specified length on the right. If the string is already longer than length then it is truncated (on the right).
    32. POSITION( s1 IN s2), POSITION(substr,str): location of specified substring
    33. PROPER(STRING1) : returns from a character expression a string capitalized as appropriate for proper names.
    34. REPEAT(string1, count1): repeats string1 count1 times
    35. REPLICATE(string1, count1): same as REPEAT(string1,count1)
    36. REPLACE(string1, string2, string3): replaces all occurrences in string1 of substring string2 with substring string3.
    37. RIGHT(string1, count1): the rightmost count1 of characters of string1
    38. RPAD(string1, length1 [, cPadCharacter]): returns a string from an expression, padded with character(a space by default) to a specified length on the right. If the string is already longer than length then it is truncated (on the right).
    39. RTRIM(string1): removes all trailing blanks in string1
    40. SOUNDEX(string1): a four character code representing the sound of string1
    41. SPACE(nSpaces): returns a character string composed of a specified number of spaces.
    42. STRCAT(string1, string2): string concatenation, you can use string1+string2 too,same as CONCAT.
    43. STRCAT(string1, string2,...): returns the string that results from concatenating the arguments, NULL values are skipped,same as CONCAT.
    44. STRCMP(expr1,expr2): returns 0 if the strings are the same, -1 if the first argument is smaller than the second, and 1 otherwise.
    45. STRCONV(expr1 [, charsetName]): returns a string by decoding the specified array of bytes using the specified charset. Cp895(Czech MS - DOS 895), Cp620(Polish MS - DOS 620) and Mazovia are extra supported although JVM doesn't support those. The omitted charsetName is 'ISO8859_1'.
    46. STRTRAN(cSearched, cSearchFor [, cReplacement][, nStartOccurrence] [, nNumberOfOccurrences]): searches a character expression or memo field for occurrences of a second character expression or memo field, and then replaces each occurrence with a third character expression or memo field.
    47. STUFF(cExpression, nStartReplacement, nCharactersReplaced, cReplacement): returns a string created by replacing a specified number of characters in a character expression with another character expression. cExpression specifies the string expression in which the replacement occurs. nStartReplacement specifies the position in cExpression where the replacement begins. nCharactersReplaced specifies the number of characters to be replaced. If nCharactersReplaced is 0, the replacement string cReplacement is inserted into cExpression. cReplacement specifies the replacement string expression. If cReplacement is the empty string, the number of characters specified by nCharactersReplaced are removed from cExpression.
    48. SUBSTR(string1, start1 [,length1]): extracts the substring starting at start1 with length length1
    49. SUBSTRING(string1 FROM start1 [FOR length1]), SUBSTRING(string1, start1 [,length1]): extracts the substring starting at start1 with length length1
    50. TRANSLATE(string1, string2, string3): any character in string1 that matches a character in the string2 is replaced by the corresponding character in the string3.
    51. TRIM([[BOTH | LEADING | TRAILING] [removedstring1] FROM] string1): remove the removedstring1 (a space by default) from the start/end/both ends of the string1.
    52. UCASE(string1): converts string1 to upper case
    53. UPPER(string1): converts string1 to upper case
    54. CHARMIRR(string1 [,lDontMirrorSpaces]): mirrors string1 at character level. string1 is the string that should be mirrored. If lDontMirrorSpaces equal to true, spaces at the end of string1 will not be mirrored but kept at the end. lDontMirrorSpaces's default value is false, which means to mirror the whole string.
    55. REVERSE(string1[,lDontMirrorSpaces]): mirrors string1 at byte level.

Date/Time Functions

    1. ADDTIME(expr,expr2): adds expr2 to expr and returns the result. expr is a date or timestamp expression, and expr2 is a time expression.
    2. CDOW(date) Returns the day-of-the-week(Sunday,Monday, Tuesday, Wednesday, Thursday, Friday,Saturday) from a given date,
    3. CMONTH(date) the name of the month
    4. CURDATE(): the current date
    5. CURTIME(): the current time
    6. DATE(): the current date
    7. DATE(expr): extracts the date part of the date or timestamp expression expr.
    8. DATEDIFF(expr,expr2): returns the number of days between the start date expr and the end date expr2. expr and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
    9. DATETIME(): the current timestamp
    10. DATE_ADD(date,INTERVAL expr type), DATE_SUB(date,INTERVAL expr type), ADDDATE(date,INTERVAL expr type), SUBDATE(date,INTERVAL expr type). For instance, SELECT DATE_ADD(date1,INTERVAL hour(now())+1 HOUR), adddate(date1,interval 3 hour) FROM test;
      type Value Expected expr Format
      MICROSECOND[S] MICROSECONDS
      MILLISECOND[S] MILLISECONDS
      SECOND SECONDS
      MINUTE MINUTES
      HOUR HOURS
      DAY DAYS
      WEEK WEEKS
      MONTH MONTHS
      QUARTER QUARTERS
      YEAR YEARS
      DECADE DECADES
      CENTURY CENTURYS
      MILLENNIUM MILLENNIUMS
      SECOND_MICROSECOND 'SECONDS.MICROSECONDS'
      MINUTE_MICROSECOND 'MINUTES.MICROSECONDS'
      MINUTE_SECOND 'MINUTES:SECONDS'
      HOUR_MICROSECOND 'HOURS.MICROSECONDS'
      HOUR_SECOND 'HOURS:MINUTES:SECONDS'
      HOUR_MINUTE 'HOURS:MINUTES'
      DAY_MICROSECOND 'DAYS.MICROSECONDS'
      DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS'
      DAY_MINUTE 'DAYS HOURS:MINUTES'
      DAY_HOUR 'DAYS HOURS'
      YEAR_MONTH 'YEARS-MONTHS'
    11. DAY(date1), DAYOFMONTH(date1): the day of the month (1-31)
    12. DAYNAME(date1): the name of the day
    13. DAYOFWEEK(date1): the day of the week (1 means Sunday)
    14. DAYOFYEAR(date1): the day of the year (1-366)
    15. EXTRACT(type FROM expr): extracts parts from the date.
      type Value Expected Result
      MICROSECOND[S] MILLISECOND*1000
      MILLISECOND[S] indicats the millisecond within the second.
      SECOND indicats the second within the minute
      MINUTE MINUTES
      HOUR HOURS
      DAY DAYS
      MONTH MONTHS
      QUARTER QUARTERS
      YEAR YEARS
      DECADE DECADES
      CENTURY CENTURYS
      MILLENNIUM MILLENNIUMS
      DOW indicates the day of the week, SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY(1~7).
      DOY indicates the day number within the year. The first day of the year has value 1.
      WEEK,WOM indicats the ordinal number of the day of the week within the current month.
      WOY indicats the ordinal number of the day of the week within the current year.
      EPOCH the current time as UTC milliseconds from the epoch(1970-01-01 00:00:00).
    16. DOW(date1) get the day of the week, SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY(1~7)
    17. FROM_DAYS(expr1): given a day number expr1, returns a DATE value.
    18. GOMONTH(expr1,numMonths) : give a date,return the date before or after a number months
    19. HOUR(time1): the hour (0-23)
    20. LAST_DAY(date1): takes a date or timestamp value and returns the corresponding date for the last day of the month.
    21. MINUTE(time1): the minute (0-59)
    22. MILLISECOND(time1): the milliseconds from the time or timestamp expression time1.
    23. MICROSECOND(time1): the microseconds from the time or timestamp expression time1.
    24. MONTH(time1): the month (1-12)
    25. MONTHNAME(date1): the name of the month
    26. NOW(): the current date and time as a timestamp
    27. QUARTER(date1): the quarter (1-4)
    28. SECOND(time1): the second (0-59)
    29. SUBTIME(expr,expr2): subtracts expr2 from expr and returns the result. expr is a date or timestamp expression, and expr2 is a time expression.
    30. SYSDATE(): the current date and time as a timestamp. Asynonym for NOW().
    31. TIME(): returns the current system time in 24-hour, eight-character string (hh:mm:ss) format.
    32. TIME(expr): extracts the time part of the time or timestamp expression expr.
    33. TIMEDIFF(expr,expr2) returns the time between the start time expr and the end time expr2. Only the time parts of the values are used in the calculation.
    34. TIMESTAMP(expr): returns the date or timestamp expression expr as a timestamp value.
    35. TIMESTAMPADD(interval, count, timestamp1): adds the integer expression count to the date or timestamp expression timestamp1. interval can be SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR, FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
    36. TIMESTAMPDIFF(interval, timestamp1, timpestamp2): returns the integer difference between the date or timestamp expressions timestamp1 and timpestamp2 (timpestamp2-timestamp1). interval can be SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR, FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
    37. TO_DAYS(date1): given a date date1, returns a day number.
    38. WEEK(date1), WEEKOFYEAR(date1): the week of this year (1-53)
    39. YEAR(date1): the year

Boolean Functions

    1. BETWEEN(expression1,expression2,expression3) : determines whether the value of an expression1 lies between the expression2 and expression3, return true or false.
    2. EMPTY(expression): determines whether an expression evaluates to empty or null. The expression you include can be a string, numeric, date, or logical expression. EMPTY() returns true, when a string is empty string, spaces, tabs, carriage returns, linefeeds, or any combination of these, numeric value equals to 0, and logical expression is false.
    3. ISBLANK(expression): determines whether an expression evaluates to empty or null. The expression you include can be a string, numeric, date, or logical expression. ISBLANK() returns true, when a string is empty string or spaces, numeric value equals to null, and logical expression is null.
    4. ISALPHA(expression): determines whether the leftmost character in a character expression is alphabetic.
    5. ISDIGIT(expression): determines whether the leftmost character of the specified character expression is a digit (0 through 9).
    6. ISNULL(expression): determines whether an expression evaluates to null. The expression you include can be a string, numeric, date, or logical expression. If expression is NULL, ISNULL() returns true, otherwise it returns false.

System Functions

    1. DATABASE(): the name of the database of this connection
    2. USER(): the user name of this connection
    3. DELETED([cTableAlias | nWorkArea]): returns a logical value that indicates whether the current record is marked for deletion.
    4. RECCOUNT([nWorkArea | cTableAlias]): returns the number of records, which includes all deleted records.
    5. RECNO([nWorkArea | cTableAlias]): returns the current record number in the current or specified table. nWorkArea specifies the work area number for a table open in another work area. cTableAlias specifies the table alias for a table open in another work area.
    6. ROWLOCKED([nWorkArea | cTableAlias]): indicates whether the current row has been locked by other process or application.
    7. SYS(0), SYS(1), SYS(2), SYS(10, nJulianDayNumber), SYS(11, dExpression | tExpression | cExpression): only for VFP.

Conversion Functions

    1. CAST(expression [AS] SQLtype1): converts value1 to another data type SQLtype1. SQLtype1 may be SQL_BIGINT(Types.BIGINT), SQL_BINARY(Types.BINARY), SQL_BIT(Types.BIT), SQL_CHAR(Types.CHAR), SQL_DATE(Types.DATE), SQL_DECIMAL(Types.DECIMAL), SQL_DOUBLE(Types.DOUBLE), SQL_FLOAT(Types.FLOAT), SQL_INTEGER(Types.INTEGER), SQL_LONGVARBINARY(Types.LONGVARBINARY), SQL_LONGVARCHAR(Types.LONGVARCHAR), SQL_REAL(Types.REAL), SQL_SMALLINT(Types.SMALLINT), SQL_TIME(Types.TIME), SQL_TIMESTAMP(Types.TIMESTAMP), SQL_TINYINT(Types.TINYINT), SQL_VARBINARY(Types.VARBINARY), SQL_VARCHAR(Types.VARCHAR), BIGINT(Types.BIGINT), BINARY(Types.BINARY), BIT(Types.BIT), CHAR(Types.CHAR), DATE(Types.DATE), DECIMAL(Types.DECIMAL), DOUBLE(Types.DOUBLE), FLOAT(Types.FLOAT), INTEGER(Types.INTEGER), LONGVARBINARY(Types.LONGVARBINARY), LONGVARCHAR(Types.LONGVARCHAR), REAL(Types.REAL), SMALLINT(Types.SMALLINT), TIME(Types.TIME), TIMESTAMP(Types.TIMESTAMP), TINYINT(Types.TINYINT), VARBINARY(Types.VARBINARY), and VARCHAR(Types.VARCHAR). For instance, cast('456' AS SQL_INTEGER),cast('123.456' AS SQL_DECIMAL), and cast('2004-12-23' as sql_date).
    2. CONVERT(value1, SQLtype1): converts value1 to another data type SQLtype1. SQLtype1 may be SQL_BIGINT(Types.BIGINT), SQL_BINARY(Types.BINARY), SQL_BIT(Types.BIT), SQL_CHAR(Types.CHAR), SQL_DATE(Types.DATE), SQL_DECIMAL(Types.DECIMAL), SQL_DOUBLE(Types.DOUBLE), SQL_FLOAT(Types.FLOAT), SQL_INTEGER(Types.INTEGER), SQL_LONGVARBINARY(Types.LONGVARBINARY), SQL_LONGVARCHAR(Types.LONGVARCHAR), SQL_REAL(Types.REAL), SQL_SMALLINT(Types.SMALLINT), SQL_TIME(Types.TIME), SQL_TIMESTAMP(Types.TIMESTAMP), SQL_TINYINT(Types.TINYINT), SQL_VARBINARY(Types.VARBINARY), and SQL_VARCHAR(Types.VARCHAR). value1 may be any complicated expression. For instance, CONVERT("123",SQL_INTEGER).
    3. CTOD(cExpression): converts a string expression to a date expression.
    4. CTOT(cCharacterExpression): returns a timestamp value from a string expression.
    5. DTOC(date1 | timestamp1): returns a string from a date or timestamp expression.
    6. DTOT(dDateExpression): returns a timestamp value from a date expression.
    7. DTOS(date1 | timestamp1): returns a string in a yyyymmdd format from a specified date or timestamp expression.
    8. TTOC(tExpression [, 1 | 2]): converts a timestamp expression to a string value of a specified format.
    9. TTOD(tExpression): returns a date value from a timestamp expression.
    10. STR(nExpression [, nLength [, nDecimalPlaces]]): Returns the character equivalent of a specified numeric expression. nExpression specifies the numeric expression STR( ) evaluates. nLength specifies the length of the character string STR( ) returns. The length includes one character for the decimal point and one character for each digit to the right of the decimal point. nDecimalPlaces specifies the number of decimal places in the character string STR( ) returns. If you specify fewer decimal places than are in nExpression, the extra digits are truncated. STR( ) pads the character string it returns with leading spaces if you specify a length larger than the number of digits to the left of the decimal point. STR( ) returns a string of asterisks, indicating numeric overflow, if you specify a length less than the number of digits to the left of the decimal point.
    11. VAL(string1): returns a numeric value from a string1 composed of numbers.
    12. DESCEND(expression): only for Clipper's NTX index file.
    13. COLLATE(string1[,collation]): For multilingual sort in ORDER BY clause. Now collation can be 'DUTCH', 'GERMAN', 'ICELAND', 'SPANISH', 'RUSSIAN', 'CZECH', 'GREEK', 'SLOVAK', 'POLISH', 'TURKISH', 'HUNGARY', and 'MAZOVIA'. Without collation parameter, COLLATE function will try to utilize charSet property in Connection properties.

Security Functions

    1. COMPRESS(content) : Return a compressed byte[]
    2. UNCOMPRESS(compressedBytes) : Return an uncompressed byte[],please don't use it for non-compressed data
    3. ENCRYPT(content,cKey,cCryptMethod): Returns a crypted byte[]. cCryptMethod should be 'DES', 'TRIDES', or 'BLOWFISH' now. ENCRYPT function is used for VARBINARY column. Data Encryption Standard (DES) algorithm, adopted by the U.S. government in 1977, is a block cipher that transforms 64-bit data blocks under a 56-bit secret key, by means of permutation and substitution. It is officially described in FIPS PUB 46. The DES algorithm is used for many applications within the government and in the private sector. Triple-DES is an improvement over DES. It uses three DES keys k1, k2 and k3. A message is encrypted with k1 first, then decrypted with k2 and encrypted again with k3 (DESencryptiondecryptionencryption). This increases security as the key length effectively increases from 56 to 112 or 168 (two or three keys may be used in TriDES). The DES key size is 128 or 192 bit and block size 64 bit.
    4. DECRYPT(content,cKey,cCryptMethod): Returns a decrypted byte[]. cCryptMethod should be 'DES', 'TRIDES', or 'BLOWFISH' now.
    5. ENCODE(content): Encodes a BASE64 encoding string.
    6. DECODE(content): Returns a byte[] from a BASE64 string.
    7. ENCODE(content,cKey,cCryptMethod): Crypts and encodes content. cCryptMethod should be 'DES', 'TRIDES', or 'BLOWFISH'. ENCRYPT function is used for VARCHAR column.
    8. DECODE(content,cKey,cCryptMethod): Decodes and decrypts content. cCryptMethod should be 'DES', 'TRIDES', or 'BLOWFISH' now.
    9. MD5(string1): Calculates a MD5 checksum for the string1.
    10. Crypt3(word[, salt]): Returns a hashed string of 13 printable ASCII characters, with the first two characters represent the salt. It can be used to accept typed passwords from the user, or attempting to crack Unix passwords with a dictionary.

Sequence Functions

    1. NEXTVAL(cSequenceName): advances sequence and returns new value.
    2. CURRVAL(cSequenceName): returns value most recently obtained with nextval.

Miscellaneous Functions

Function
Argument Type
Return Type
Description

GREATEST(expression1,expression2[,...])

MAX(expression1,expression2[,...])

 any numeric, string, date/time, or boolean type same as argument type maximum value of all expressions

LEAST(expression1,expression2[,...])

MIN(expression1,expression2[,...])

 any numeric, string, date/time, or boolean type same as argument type minimum value of all expressions

IF(lExpression, eExpression1, eExpression2)

IIF(lExpression, eExpression1, eExpression2)

 

lExpression specifies the logical expression that IF()/IIF( ) evaluates.

 Returns one of two values depending on the value of a logical expression.
 If lExpression evaluates to true , eExpression1 is returned. If lExpression evaluates to false, eExpression2 is returned.

NVL(expression, value)

IFNULL(expression, value)

 any numeric, string, date/time, or boolean type Returns one of two values depending on whether expression is null. If expression evaluates to null , value is returned. Otherwise, expression is returned.
INLIST(eExpression1, eExpression2 [, eExpression3 ...]) eExpression1 specifies the expression INLIST( ) searches for in the set of expressions. eExpression2 [, eExpression3 ...] specifies the set of expressions to search. You must include at least one expression (eExpression2), and can include up to 24 expressions (eExpression2, eExpression3, and so on). Determines whether an expression matches another expression in a set of expressions. All the expressions in the set of expressions must be of the same data type.
COALESCE(value [, ...]) any numeric, string, date/time, or boolean type the type of the first of its arguments that is not null returns the first of its arguments that is not null
ELT(numberExpression,value1Expression,[value2Expression,...]) numberExpression must be a integer type,value expression can be any type Returns value depending on the numberExpression,value1Expression,...valuexExpression Returns value1Expression if numberExpression = 1, value2Expression if numberExpression = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments.
INTERVAL(expression,expr1,expr2,...,exprn) any numeric, string, date/time, or boolean type integer value returns 0 if expression< expr1, 1 if expression< expr2 and so on or -1 if expressionN is NULL. If expression>exprn, returns n.
TRANSFER (expression, search_1, result_1)
TRANSFER (expression, search_1, result_1, search_2, result_2)
TRANSFER (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)
TRANSFER (expression, search_1, result_1, default)
TRANSFER (expression, search_1, result_1, search_2, result_2, default)
TRANSFER (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default) 		any numeric, string, date/time, or boolean type,or null Returns value depending on the expression,search_x,result_x and default TRANSFER compares expression to the search_x expressions and, if matches, returns result_x. If not, returns default, or, if default is left out, return null .
TRANSFORM(expression, formatcodes) expression specifies the character, currency, date, or numeric expression to format.
formatcode specifies one format code that determine how the expression is formatted. The following table lists the available format codes
Format CodeDescription
@CCR is appended to positive currency or numeric values to indicate a credit.
@Dact as DTOS function.
@Eact as DTOS function.
@Tleading and trailing spaces are trimmed from character values.
@Xdb is appended to negative currency or numeric values to indicate a debit.
@Zif 0, currency or numeric values are converted to spaces.
@(encloses negative currency or numeric values in parentheses.
@^converts currency or numeric values to scientific notation.
@0converts numeric or currency values to their hexadecimal equivalents. The numeric or currency value must be positive and less than 4,294,967,296.
! converts a character to uppercase.
$adds the current currency symbol specified by SET CURRENCY to currency and numeric values. By default, the symbol is placed immediately before or after the value. However, the currency symbol and its placement (specified with SET CURRENCY), the separator character (specified with SET SEPARATOR) and the decimal character (specified with SET POINT) can all be changed.
Xspecifies the width of character values. For example, if cFormatCodes is ‘XX? 2 characters are returned.
Yconverts logical true (.T.) and false (.F.) values to Y and N, respectively.
@!converts a string to uppercase.
return the formatted string returns a character string from an expression in a format determined by a format code

Aggregate Functions

    1. AVG(expression): the average (arithmetic mean) of all input values.
    2. COUNT(*): the number of input values.
    3. COUNT(expression): the number of input values for which the value of expression is not null.
    4. MAX(expression): the maximum value of expression across all input values.
    5. MIN(expression): the minimum value of expression across all input values.
    6. STD(expression): the sample standard deviation of the input values.
    7. STDDEV(expression): the sample standard deviation of the input values.
    8. SUM(expression): the sum of expression across all input values.

     

Copyright © 2005 Hongxin Technology & Trade Ltd. | All Rights Reserved. |