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. Regular Expresson Functions
  11. Miscellaneous Functions
  12. Aggregate Functions

Mathematical Functions

    1. ABS(x): the absolute value
    2. BITAND(x, y): returns the result of performing a bitwise AND on x and y.
    3. BIT_COUNT(x): returns the number of bits of x.
    4. CEIL(x), CEILING(x): the smallest integer that is not less than x
    5. DEGREES(x): converts radians to degrees
    6. EXP(x): exponential, e(2.718...) raised to the power of x
    7. FLOOR(x): the largest integer not greater than argument x
    8. INT(x) : truncates x to nearest integer
    9. LOG(x), LN(x): the natural logarithm
    10. LOG(b,x): returns the logarithm of X for an arbitary base B
    11. LOG10(x): the base 10 logarithm
    12. LOG2(X): the base 2 logarithm
    13. LN(x): the natural logarithm
    14. MOD(y, x): the remainder of y/x, you can use y%x too.
    15. PI(): pi constant, 3.14159265358979323846.
    16. POW(x, y), POWER(x, y): x raised to the power of y
    17. RADIANS(x): converts degrees to radians
    18. RAND([seed]): a random value between 0.0 and 1.0
    19. ROUND(x [,y]): rounds x to nearest integer without y, or round x to y digits after the decimal point.
    20. SIGN(x): returns -1 if x is smaller than 0, 0 if x==0 and 1 if x is bigger than 0.
    21. SQRT(x): the square root
    22. 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),ATN(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. ATC(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, RAT( ) returns 0. It is case insensitive.
    5. 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.
    6. BIT_LENGTH(string1): the length of the string str in bits
    7. CHAR_LENGTH(string1), CHARACTER_LENGTH(string1): the number of characters in string1
    8. CHAR(integer), CHR(integer): a character with the given ASCII code
    9. 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.
    10. 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.
    11. CONCAT(string1, string2): string concatenation, you can use string1+string2 too.
    12. CONCAT(string1, string2,...): returns the string that results from concatenating the arguments. NULL values are skipped.
    13. 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.
    14. 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.
    15. DIFFERENCE(string1, string2): the difference between the sound of string1 and string2
    16. 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.
    17. INITCAP(string1): converts first letter of each word (whitespace-separated) to upper case
    18. INSERT(string1, start1, length1, string2): a string where length1 number of characters beginning at start1 has been replaced by string2
    19. INSTR(string1, string2 [,start1]): the first index (>0:left location, 0:not found) where string2 is found in string1, starting at start1
    20. INSTR(start1, string1, string2): (Compatible purpose) the first index (>0:left location, 0:not found) where string2 is found in string1, starting at start1.
    21. LCASE(string1): converts string1 to lower case
    22. LEFT(string1, count1): the leftmost count1 of characters of string1
    23. LENGTH(string1), LEN(string1): the number of characters in string1
    24. LOCATE(string1, string2 [,start1]): the first index (>0:left location, 0:not found) where string1 is found in string2, starting at start1
    25. LOWER(string1): converts string1 to lower case
    26. 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).
    27. LTRIM(string1): removes all leading blanks in string1
    28. 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.
    29. 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.
    30. OCTET_LENGTH(string1): the number of octets (8-bit bytes) needed to represent the string1.
    31. 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).
    32. 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).
    33. 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).
    34. RAT(cSearchExpression, cExpressionSearched [, nOccurrence]): returns the beginning numeric position of the last (rightmost) occurrence of a character expression or memo field within another character expression or memo field, counting from the rightmost character. If the character expression isn't found, RAT( ) returns 0.
    35. RATC(cSearchExpression, cExpressionSearched [, nOccurrence]): returns the beginning numeric position of the last (rightmost) occurrence of a character expression or memo field within another character expression or memo field, counting from the rightmost character. If the character expression isn't found, RATC( ) returns 0. It is case insensitive.
    36. POSITION( s1 IN s2), POSITION(substr,str): location of specified substring
    37. PROPER(STRING1) : returns from a character expression a string capitalized as appropriate for proper names.
    38. REPEAT(string1, count1): repeats string1 count1 times
    39. REPLICATE(string1, count1): same as REPEAT(string1,count1)
    40. REPLACE(string1, string2, string3): replaces all occurrences in string1 of substring string2 with substring string3.
    41. RIGHT(string1, count1): the rightmost count1 of characters of string1
    42. 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).
    43. RTRIM(string1): removes all trailing blanks in string1
    44. SOUNDEX(string1): a four character code representing the sound of string1
    45. SPACE(nSpaces): returns a character string composed of a specified number of spaces.
    46. SPLIT(string1, string2): split string1 according to delimiter string2, and return an String[] object (Types.ARRAY). Special SPLIT(expression,'') will return strings which contains only Letter and Digit. Special SPLIT(expression,null) will split string into length=1 strings, which is only Letter or Digit.
    47. STRCAT(string1, string2): string concatenation, you can use string1+string2 too,same as CONCAT.
    48. STRCAT(string1, string2,...): returns the string that results from concatenating the arguments, NULL values are skipped,same as CONCAT.
    49. STRCMP(expr1,expr2): returns 0 if the strings are the same, -1 if the first argument is smaller than the second, and 1 otherwise.
    50. 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'.
    51. 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.
    52. 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.
    53. SUBSTR(string1, start1 [,length1]): extracts the substring starting at start1 with length length1
    54. SUBSTRING(string1 FROM start1 [FOR length1]), SUBSTRING(string1, start1 [,length1]): extracts the substring starting at start1 with length length1
    55. TRANSLATE(string1, string2, string3): any character in string1 that matches a character in the string2 is replaced by the corresponding character in the string3.
    56. TRIM([[BOTH | LEADING | TRAILING] [removedstring1] FROM] string1): remove the removedstring1 (a space by default) from the start/end/both ends of the string1.
    57. UCASE(string1): converts string1 to upper case
    58. UPPER(string1): converts string1 to upper case
    59. 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.
    60. 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. DATETIME(): the current timestamp
    9. DATESERIAL(year,month,day): returns a date value representing a specified year, month, and day.
    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. 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.
    12. DATEADD(INTERVAL, expr, date), DATEDIFF (INTERVAL, date1, date2), DATEPART (INTERVAL, date) For instance, SELECT DateAdd('m', 3, date1) FROM test;
      INTERVAL Value Expected expr Format
      yyyy Year
      q Quarter
      m Month
      y Day of the year
      d Day
      w Weekday
      ww Week
      h Hour
      n Minute
      s Second
    13. DAY(date1), DAYOFMONTH(date1): the day of the month (1-31)
    14. DAYNAME(date1): the name of the day
    15. DAYOFWEEK(date1): the day of the week (1 means Sunday)
    16. DAYOFYEAR(date1): the day of the year (1-366)
    17. 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).
    18. DOW(date1) get the day of the week, SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY(1~7)
    19. FROM_DAYS(expr1): given a day number expr1, returns a DATE value.
    20. GOMONTH(expr1,numMonths) : give a date,return the date before or after a number months
    21. HOUR(time1): the hour (0-23)
    22. LAST_DAY(date1): takes a date or timestamp value and returns the corresponding date for the last day of the month.
    23. MINUTE(time1): the minute (0-59)
    24. MILLISECOND(time1): the milliseconds from the time or timestamp expression time1.
    25. MICROSECOND(time1): the microseconds from the time or timestamp expression time1.
    26. MONTH(time1): the month (1-12)
    27. MONTHNAME(date1): the name of the month
    28. NOW(): the current date and time as a timestamp
    29. QUARTER(date1): the quarter (1-4)
    30. SECOND(time1): the second (0-59)
    31. SEC(time1) (Compatible purpose): the second (0-59)
    32. SUBTIME(expr,expr2): subtracts expr2 from expr and returns the result. expr is a date or timestamp expression, and expr2 is a time expression.
    33. SYSDATE(): the current date and time as a timestamp. Asynonym for NOW().
    34. TIME(): returns the current system time in 24-hour, eight-character string (hh:mm:ss) format.
    35. TIME(expr): extracts the time part of the time or timestamp expression expr.
    36. 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.
    37. TIMESERIAL(hour,minute,second): returns a Time value representing a specified hour, minute, and second.
    38. TIMESTAMP(expr): returns the date or timestamp expression expr as a timestamp value.
    39. 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.
    40. 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.
    41. TO_DAYS(date1): given a date date1, returns a day number.
    42. WEEK(date1), WEEKOFYEAR(date1): the week of this year (1-53)
    43. 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. ISDATE(expression): determines whether an expression can be converted to a date value.
    6. ISDIGIT(expression): determines whether the leftmost character of the specified character expression is a digit (0 through 9).
    7. ISDIGITS(expression): determines whether a string contains only digits(0 through 9).
    8. 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.
    9. ISNUMERIC(expression): determines whether an expression can be converted to a number value.

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([cTableAlias | nWorkArea]): returns the number of records, which includes all deleted records.
    5. RECNO([cTableAlias | nWorkArea]): 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([cTableAlias | nWorkArea]): indicates whether the current row has been locked by process or application.
    7. TABLELOCKED(cTableName): indicates whether a table has been locked by process or application. For instance, select tablelocked('test').
    8. CURRVAL(cTableName, cColumnName): returns the last generated IDENTITY(auto_increment) value for a particuar table. If that table hasn't IDENTITY(auto_increment) column, it will return silently null value. Only when there's more than one auto_increment column in a table, it will check cColumnName's validity.
    9. LAST_INSERT_ID(): returns an INT (32-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column.

Conversion Functions

    1. CAST(expression AS data_type): converts value1 to another data type data_type. data_type 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), INT(Types.INTEGER), LONGVARBINARY(Types.LONGVARBINARY), LONGVARCHAR(Types.LONGVARCHAR), NUMERIC (Types.NUMERIC), 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 DECIMAL(12,5)), 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), INT(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. CBOOL(expression): returns a Boolean value from an expression.
    4. CBYTE(expression): returns a Byte value from an expression.
    5. CCUR(expression): returns a Currency value with four decimal digits of precision to the right of the decimal from an expression.
    6. CDATE(expression,pattern): returns a Date value according a pattern from an expression. For instance, CDATE('21111947','ddMMyyyy').
    7. CDBL(expression): returns a Double value from an expression.
    8. CINT(expression): returns an Integer value from an expression.
    9. CLNG(expression): returns a Long value from an expression.
    10. CSNG(expression): returns a Float value from an expression.
    11. CSTR(expression): returns a String value from an expression.
    12. CTOD(cExpression): converts a string expression to a date expression.
    13. CTOT(cExpression): returns a timestamp value from a string expression.
    14. DTOC(date1 | timestamp1[, 1]): returns a string from a date or timestamp expression.
    15. DTOT(dDateExpression): returns a timestamp value from a date expression.
    16. LTOC(bExpression): returns a string value(T,F, or ' ') from a logical expression.
    17. DTOS(date1 | timestamp1): returns a string in a yyyymmdd format from a specified date or timestamp expression.
    18. DTOS(date1 | timestamp1,pattern): returns a string according to a pattern format from a specified date or timestamp expression.
    19. TTOC(tExpression [, 1 | 2]): converts a timestamp expression to a string value of a specified format.
    20. TTOD(tExpression): returns a date value from a timestamp expression.
    21. POSIXTOT(expression): returns a timestamp value from a POSIX timestamp value.
    22. TTOPOSIX(tExpression): converts a timestamp expression to a POSIX timestamp value.
    23. 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. If nLength is omitted, nLength defaults to 10 characters.
    24. STRZERO(nExpression, nLength[, nDecimals]): convert a numeric expression to a string padded with leading zeros.
    25. VAL(string1): returns a numeric value from a string1 composed of numbers.
    26. 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', CP850, CP852, CP866, CROATIAN, HEBREW, SWEDISH, and 'MAZOVIA'. Without collation parameter, COLLATE function will try to utilize charSet property in Connection properties.
    27. PasToJava(str): get a Java string from a Pascal-style string
    28. JavaToPas(str): get a Pascal-style string from a Java string
    29. PasToJava(str): get a null-terminated string from a Pascal-style string
    30. CToPas(str): get a Pascal-style string from a null-terminated string
    31. CToJava(str): get a Java string from a null-terminated string
    32. JavaToC(str): get a null-terminated from a Java string
    33. BToInt_LE(binary): get int value from bytes with little-endian.
    34. BToInt_BE(binary): get int value from bytes with big-endian.
    35. IntToB_LE(binary): get bytes with little-endian from int value.
    36. IntToB_BE(binary): get bytes with big-endian from int value.
    37. BToShort_LE(binary): get short value from bytes with little-endian.
    38. BToShort_BE(binary): get short value from bytes with big-endian.
    39. ShortToB_LE(binary): get bytes with little-endian from short value.
    40. ShortToB_BE(binary): get bytes with big-endian from short value.
    41. BToLong_LE(binary): get long value from bytes with little-endian.
    42. BToLong_BE(binary): get long value from bytes with big-endian.
    43. LongToB_LE(binary): get bytes with little-endian from long value.
    44. LongToB_BE(binary): get bytes with big-endian from long value.
    45. GetNumber(str[, defaultValue]): return a number value(int, long, double) according to str. If failed to parse, return defaultValue(null is omitted value).
    46. GetInt(str[, defaultValue]): return an int value according to str. If failed to parse, return defaultValue(null is omitted value).
    47. GetLong(str[, defaultValue]): return a long value according to str. If failed to parse, return defaultValue(null is omitted value).
    48. GetDouble(str[, defaultValue]): return a double value according to str. If failed to parse, return defaultValue(null is omitted value).
    49. FORMAT(expression , format): eturns a string containing an expression formatted according to instructions contained in a format expression. Now format supports only date/timestamp pattern.

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', 'BLOWFISH', or 'AES' 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.
      Blowfish is a keyed, symmetric block cipher, designed in 1993 by Bruce Schneier and included in a large number of cipher suites and encryption products. Blowfish has a 64-bit block size and a variable key length from 32 bits up to 448 bits. It is a 16-round Feistel cipher and uses large key-dependent S-boxes.
      The Advanced Encryption Standard (AES) is a specification for the encryption of electronic data established by the U.S. National Institute of Standards and Technology (NIST) in 2001. Originally called Rijndael, the cipher was developed by two Belgian cryptographers, Joan Daemen and Vincent Rijmen, who submitted to the AES selection process. The algorithm described by AES is a symmetric-key algorithm, meaning the same key is used for both encrypting and decrypting the data. AES is a variant of Rijndael which has a fixed block size of 128 bits, and a key size of 128, 192, or 256 bits.
    4. DECRYPT(content,cKey,cCryptMethod): Returns a decrypted byte[]. cCryptMethod should be 'DES', 'TRIDES', 'BLOWFISH', or 'AES' 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', 'BLOWFISH', or 'AES'. ENCRYPT function is used for VARCHAR column.
    8. DECODE(content,cKey,cCryptMethod): Decodes and decrypts content. cCryptMethod should be 'DES', 'TRIDES', 'BLOWFISH', or 'AES' now.
    9. MD5(string1): Calculates a MD5(Message-Digest Algorithm 5) checksum for the string1.
    10. SHA1(string1): Calculates a SHA-1(Secure Hash Algorithm 1) hash for the string1.
    11. 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.

Regular Expresson Functions

Function
Argument Type
Return Type
Description

REGEXP_LIKE(expression,pattern[,match_parameter])

expression is a string expression that serves as the search value.
pattern is the regular expression. For a listing of the operators you can specify in pattern, refer to java.util.regex.Matcher.Pattern.
match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_parameter:
'i' specifies case-insensitive matching.
'c' specifies case-sensitive matching.
'n' allows the period (.), which is the match-any-character wildcard character, to match the newline character. If you omit this parameter, then the period does not match the newline character.
'm' treats the source string as multiple lines. HXTT Access interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, then HXTT Access treats the source string as a single line.
'x' ignores whitespace characters. By default, whitespace characters match themselves.
If you specify multiple contradictory values, then HXTT Access uses the last value. For example, if you specify 'ic', then HXTT Access uses case-sensitive matching. If you specify a character other than those shown above, then HXTT Access will ignore it.
If you omit match_parameter, then:
The default case sensitivity is determined by the value of the caseInsensitive connection property.
A period (.) does not match the newline character.
The source string is treated as a single line.
return true if it find one subsequence matches the pattern. REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the charSet connection property.

REGEXP_REPLACE(source_expression,pattern[, cReplacement[, nStartOccurrence [, nNumberOfOccurrences[,match_parameter]]]])

source_expression is a string expression that serves as the search value.
pattern is the regular expression. IFor a listing of the operators you can specify in pattern, refer to java.util.regex.Matcher.Pattern.
cReplacement may contain references to subsequences captured during the previous match: Each occurrence of $g will be replaced by the result of evaluating group(g). The first number after the $ is always treated as part of the group reference. Subsequent numbers are incorporated into g if they would form a legal group reference. Only the numerals '0' through '9' are considered as potential components of the group reference. If the second group matched the string "foo", for example, then passing the replacement string "$2bar" would cause "foobar" to be appended to the string buffer. A dollar sign ($) may be included as a literal in the replacement string by preceding it with a backslash (\$).
nStartOccurrence is a positive integer indicating the character of source_expression where HXTT Access should begin the search. The default is 1, meaning that HXTT Accessbegins the search at the first character of source_expression.
nNumberOfOccurrences is a nonnegative integer indicating the occurrence of the replace operation:
If you specify 0, then HXTT Access replaces all occurrences of the match.
If you specify a positive integer n, then HXTT Access replaces the nth occurrence.
If occurrence is greater than 1, then the database searches for the second occurrence beginning with the first character following the first occurrence of pattern, and so forth. This behavior is different from the INSTR function, which begins its search for the second occurrence at the second character of the first occurrence.
match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_parameter:
'i' specifies case-insensitive matching.
'c' specifies case-sensitive matching.
'n' allows the period (.), which is the match-any-character wildcard character, to match the newline character. If you omit this parameter, then the period does not match the newline character.
'm' treats the source string as multiple lines. HXTT Access interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, then HXTT Access treats the source string as a single line.
'x' ignores whitespace characters. By default, whitespace characters match themselves.
If you specify multiple contradictory values, then HXTT Access uses the last value. For example, if you specify 'ic', then HXTT Access uses case-sensitive matching. If you specify a character other than those shown above, then HXTT Access will ignore it.
If you omit match_parameter, then:
The default case sensitivity is determined by the value of the caseInsensitive connection property.
A period (.) does not match the newline character.
The source string is treated as a single line.
searches a character expression for occurrences of a pattern, and then replaces each occurrence with a third pattern expression. REGEXP_REPLACE extends the functionality of the STRTRAN function by letting you search a string for a regular expression pattern. By default, the function returns source_expression with every occurrence of the regular expression pattern replaced with replace_string. The string returned is in the same character set as source_expression.

REGEXP_INSTR(source_expression,pattern[, nStartOccurrence [, nNumberOfOccurrences[,return_option,[,match_parameter[,subexpr ]]]]])

source_expression is a string expression that serves as the search value.
pattern is the regular expression. IFor a listing of the operators you can specify in pattern, refer to java.util.regex.Matcher.Pattern.
nStartOccurrence is a positive integer indicating the character of source_expression where HXTT Access should begin the search. The default is 1, meaning that HXTT Accessbegins the search at the first character of source_expression.
nNumberOfOccurrences is a nonnegative integer indicating the occurrence of the replace operation:
If you specify 0, then HXTT Access replaces all occurrences of the match.
If you specify a positive integer n, then HXTT Access replaces the nth occurrence.
If occurrence is greater than 1, then the database searches for the second occurrence beginning with the first character following the first occurrence of pattern, and so forth. This behavior is different from the INSTR function, which begins its search for the second occurrence at the second character of the first occurrence.
return_option lets you specify what HXTT Access should return in relation to the occurrence:
If you specify 0, then HXTT Access returns the position of the first character of the occurrence. This is the default.
If you specify 1, then HXTT Access returns the position of the character following the occurrence.
match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_parameter:
'i' specifies case-insensitive matching.
'c' specifies case-sensitive matching.
'n' allows the period (.), which is the match-any-character wildcard character, to match the newline character. If you omit this parameter, then the period does not match the newline character.
'm' treats the source string as multiple lines. HXTT Access interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, then HXTT Access treats the source string as a single line.
'x' ignores whitespace characters. By default, whitespace characters match themselves.
If you specify multiple contradictory values, then HXTT Access uses the last value. For example, if you specify 'ic', then HXTT Access uses case-sensitive matching. If you specify a character other than those shown above, then HXTT Access will ignore it.
If you omit match_parameter, then:
The default case sensitivity is determined by the value of the caseInsensitive connection property.
A period (.) does not match the newline character.
The source string is treated as a single line. For a pattern with subexpressions, the subexpr is a fragment of pattern enclosed in parentheses. Subexpressions can be nested. Subexpressions are numbered in order in which their left parentheses appear in pattern. For example, consider the following expression:
0123(((abc)(de)f)ghi)45(678)
This expression has five subexpressions in the following order: "abcdefghi" followed by "abcdef", "abc", "de" and "678".
If subexpr is zero, then the position of the entire substring that matches the pattern is returned. If subexpr is greater than zero, then the position of the substring fragment that corresponds to subexpression number subexpr in the matched substring is returned. If pattern does not have at least subexpr subexpressions, the function returns zero. The default value for subexpr is zero.
returns the position of the first character of the occurrence or the character following the occurrence. REGEXP_INSTR extends the functionality of the INSTR function by letting you search a string for a regular expression pattern. It returns an integer indicating the beginning or ending position of the matched substring, depending on the value of the return_option argument. If no match is found, then the function returns 0.

REGEXP_SUBSTR (source_expression,pattern[, nStartOccurrence [, nNumberOfOccurrences[,match_parameter[,subexpr ]]]]))

source_expression is a string expression that serves as the search value.
pattern is the regular expression. IFor a listing of the operators you can specify in pattern, refer to java.util.regex.Matcher.Pattern.
nStartOccurrence is a positive integer indicating the character of source_expression where HXTT Access should begin the search. The default is 1, meaning that HXTT Accessbegins the search at the first character of source_expression.
nNumberOfOccurrences is a nonnegative integer indicating the occurrence of the replace operation:
If you specify 0, then HXTT Access replaces all occurrences of the match.
If you specify a positive integer n, then HXTT Access replaces the nth occurrence.
If occurrence is greater than 1, then the database searches for the second occurrence beginning with the first character following the first occurrence of pattern, and so forth. This behavior is different from the INSTR function, which begins its search for the second occurrence at the second character of the first occurrence.
match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_parameter:
'i' specifies case-insensitive matching.
'c' specifies case-sensitive matching.
'n' allows the period (.), which is the match-any-character wildcard character, to match the newline character. If you omit this parameter, then the period does not match the newline character.
'm' treats the source string as multiple lines. HXTT Access interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, then HXTT Access treats the source string as a single line.
'x' ignores whitespace characters. By default, whitespace characters match themselves.
If you specify multiple contradictory values, then HXTT Access uses the last value. For example, if you specify 'ic', then HXTT Access uses case-sensitive matching. If you specify a character other than those shown above, then HXTT Access will ignore it.
If you omit match_parameter, then:
The default case sensitivity is determined by the value of the caseInsensitive connection property.
A period (.) does not match the newline character.
The source string is treated as a single line.
For a pattern with subexpressions, the subexpr is a fragment of pattern enclosed in parentheses. Subexpressions can be nested. Subexpressions are numbered in order in which their left parentheses appear in pattern. For example, consider the following expression:
0123(((abc)(de)f)ghi)45(678)
This expression has five subexpressions in the following order: "abcdefghi" followed by "abcdef", "abc", "de" and "678".
If subexpr is zero, then the position of the entire substring that matches the pattern is returned. If subexpr is greater than zero, then the position of the substring fragment that corresponds to subexpression number subexpr in the matched substring is returned. If pattern does not have at least subexpr subexpressions, the function returns zero. The default value for subexpr is zero.
returns the substring of the occurrence. REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. It returns the substring itself. This function is useful if you need the contents of a match string but not its position in the source string.

REGEXP_COUNT(source_expression,pattern[, nStartOccurrence[,match_parameter]])

source_expression is a string expression that serves as the search value.
pattern is the regular expression. IFor a listing of the operators you can specify in pattern, refer to java.util.regex.Matcher.Pattern.
nStartOccurrence is a positive integer indicating the character of source_expression where HXTT Access should begin the search. The default is 1, meaning that HXTT Accessbegins the search at the first character of source_expression.
match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_parameter:
'i' specifies case-insensitive matching.
'c' specifies case-sensitive matching.
'n' allows the period (.), which is the match-any-character wildcard character, to match the newline character. If you omit this parameter, then the period does not match the newline character.
'm' treats the source string as multiple lines. HXTT Access interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, then HXTT Access treats the source string as a single line.
'x' ignores whitespace characters. By default, whitespace characters match themselves.
If you specify multiple contradictory values, then HXTT Access uses the last value. For example, if you specify 'ic', then HXTT Access uses case-sensitive matching. If you specify a character other than those shown above, then HXTT Access will ignore it.
If you omit match_parameter, then:
The default case sensitivity is determined by the value of the caseInsensitive connection property.
A period (.) does not match the newline character.
The source string is treated as a single line.
returns the number of times a pattern occurs in a source string. REGEXP_COUNT returns the number of times a pattern occurs in a source string. It returns an integer indicating the number of occurrences of pattern. If no match is found, then the function returns 0.

Miscellaneous Functions

Function
Argument Type
Return Type
Description

DECODE( expression , search , result [, search , result]... [, default] )

expression is the value to compare. search is the value that is compared against expression. result is the value returned, if expression is equal to search. default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found). same as argument type The decode function has the functionality of an IF-THEN-ELSE statement.

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
  1. LASTNVL(column): returns the current value for a particuar column. When a null value is encountered, it will try to return the nearest non-null value of prior rows in the specified table. That function is useful when data are stored by using suppression of repeated data.
  2. LASTEVL(column): returns the current value for a particuar column. When an empty value is encountered, it will try to return the nearest non-empty value of prior rows in the specified table. That function is useful when data are stored by using suppression of repeated data. It means empty value when it is a null value, or 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.

Aggregate Functions

    1. FIRST(expression): the value of a specified field in the first record, respectively, of the result set returned by a query. Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by this functions will be arbitrary.
    2. LAST(expression): the value of a specified field in the last record, respectively, of the result set returned by a query. Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by this functions will be arbitrary.
    3. AVG(expression): the average (arithmetic mean) of all input values.
    4. AVG(DISTINCT expression): the average (arithmetic mean) of the distinct values of expression.
    5. COUNT(*): the number of input values.
    6. COUNT(expression): the number of input values for which the value of expression is not null.
    7. COUNT(DISTINCT expression): the number of different non-NULL values.
    8. MAX(expression): the maximum value of expression across all input values.
    9. MIN(expression): the minimum value of expression across all input values.
    10. STD(expression): the sample standard deviation of the input values.
    11. STDDEV(expression): the sample standard deviation of the input values.
    12. SUM(expression): the sum of expression across all input values.
    13. SUM(DISTINCT expression): the sum of the distinct values of expression.
    14. GROUP_CONCAT([DISTINCT] expr_list [order_by_clause] [SEPARATOR str_val]): returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. SEPARATOR is followed by the string value that should be inserted between values of result. The default is a comma (','). You can eliminate the separator altogether by specifying SEPARATOR ''. The result will be truncated to the maximum length of 8192 sometimes.
    15. GROUPING(expression): returns a value of 1 when the expression is added by either the CUBE or ROLLUP operator, or 0 when the expression is not the result of CUBE or ROLLUP. It is used to distinguish the null values that are returned by CUBE and ROLLUP from standard null values. The NULL returned as the result of a CUBE or ROLLUP operation is a special use of NULL.

     

Copyright © 1999-2012 Hongxin Technology & Trade Ltd. | All Rights Reserved. |