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
13. Table Functions
14. ETL 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. It is case sensitive. For instance, select at('a','efghiajk');
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, ATC( ) returns 0. It is case insensitive. For instance,select at('a','efghiAjk');
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. CONCATENATE(string1, string2): string concatenation, you can use string1+string2 too.
13. CONCAT(string1, string2,...): returns the string that results from concatenating the arguments. NULL values are skipped.
14. 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.
15. 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.
16. DIFFERENCE(string1, string2): the difference between the sound of string1 and string2
17. 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.
    
18. INITCAP(string1): converts first letter of each word (whitespace-separated) to upper case
19. INSERT(string1, start1, length1, string2): a string where length1 number of characters beginning at start1 has been replaced by string2
20. INSTR(string1, string2 [,start1]): the first index (>0:left location, 0:not found) where string2 is found in string1, starting at start1
21. INSTR(start1, string1, string2): (Compatible purpose) the first index (>0:left location, 0:not found) where string2 is found in string1, starting at start1.
22. LCASE(string1): converts string1 to lower case
23. LEFT(string1, count1): the leftmost count1 of characters of string1
24. LENGTH(string1), LEN(string1): the number of characters in string1
25. LOCATE(string1, string2 [,start1]): the first index (>0:left location, 0:not found) where string1 is found in string2, starting at start1
26. LOWER(string1): converts string1 to lower case
27. 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).
28. LTRIM(string1): removes all leading blanks in string1
29. 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.
30. 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.
    
31. OCTET_LENGTH(string1): the number of octets (8-bit bytes) needed to represent the string1.
32. 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).
    
33. 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).
34. 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).
35. 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.
36. 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.
37. POSITION( s1 IN s2), POSITION(substr,str): location of specified substring
38. PROPER(STRING1) : returns from a character expression a string capitalized as appropriate for proper names.
39. REPEAT(string1, count1): repeats string1 count1 times
40. REPLICATE(string1, count1): same as REPEAT(string1,count1)
41. REPLACE(string1, string2, string3): replaces all occurrences in string1 of substring string2 with substring string3.
42. RIGHT(string1, count1): the rightmost count1 of characters of string1
43. 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).
44. RTRIM(string1): removes all trailing blanks in string1
45. SOUNDEX(string1): a four character code representing the sound of string1
46. SPACE(nSpaces): returns a character string composed of a specified number of spaces.
47. 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.
48. SPLIT_PART(string, delimiter,field_number): Splits string at occurrences of delimiter and returns the field_number'th field (counting from one), or when field_number is negative, returns the |field_number|'th-from-last field.
49. STRCAT(string1, string2): string concatenation, you can use string1+string2 too,same as CONCAT.
50. STRCAT(string1, string2,...): returns the string that results from concatenating the arguments, NULL values are skipped,same as CONCAT.
51. STRCMP(expr1,expr2): returns 0 if the strings are the same, -1 if the first argument is smaller than the second, and 1 otherwise.
52. 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'.
53. 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.
    
54. 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.
    
55. SUBSTR(string1, start1 [,length1]): extracts the substring starting at start1 with length length1
56. SUBSTRING(string1 FROM start1 [FOR length1]), SUBSTRING(string1, start1 [,length1]): extracts the substring starting at start1 with length length1
57. SUBSTRING_INDEX(str,delim[,count=1]): Returns the substring from string str before 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. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.
58. SUBSTRING_ENCLOSED(str[,leftToken,rightToken]): Returns the first substring from string str which is enclosed by leftToken and rightToken. If failed to find, str will return.SUBSTRING_ENCLOSED() performs a case-sensitive match when searching. select SUBSTRING_ENCLOSED('Tom(cat)'),SUBSTRING_ENCLOSED('WhyCat is','Why','is'); will return "cat Cat".
59. SUBSTRING_ENCLOSED_LAST(str[,leftToken,rightToken]): Returns the last substring from string str which is enclosed by leftToken and rightToken. If failed to find, str will return.SUBSTRING_ENCLOSED() performs a case-sensitive match when searching. select SUBSTRING_ENCLOSED_Last('Tom(cat)(kitty)'); will return "kitty".
60. TRANSLATE(string1, string2, string3): any character in string1 that matches a character in the string2 is replaced by the corresponding character in the string3.
61. TRIM([[BOTH | LEADING | TRAILING] [removedstring1] FROM] string1): remove the removedstring1 (a space by default) from the start/end/both ends of the string1.
62. UCASE(string1): converts string1 to upper case
63. UPPER(string1): converts string1 to upper case
64. 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.
65. 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(date[,mode]), WEEKOFYEAR(date): the week of this year
    date is the date you want the week number returned from.
    mode is a number that specifies whether the week should start on Sunday or Monday and whether the week should be in the range 0 to 53 or 1 to 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. 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.
   
10. 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').

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), VARCHAR(Types.VARCHAR), JSON, and jsonb. 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), SQL_VARCHAR(Types.VARCHAR), JSON, and jsonb. 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 'GENERAL', 'MACHINE', '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. 'Excel' is a special collation for ORDER BY clause, which can detect numeric value from string value, and sort like MS Excel.
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).

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'. ENCODE 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 Text (CSV) 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 Text (CSV) 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 Text (CSV) uses the last value. For example, if you specify 'ic', then HXTT Text (CSV) uses case-sensitive matching. If you specify a character other than those shown above, then HXTT Text (CSV) 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 Text (CSV) should begin the search. The default is 1, meaning that HXTT Text (CSV)begins 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 Text (CSV) replaces all occurrences of the match. If you specify a positive integer n, then HXTT Text (CSV) 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 Text (CSV) 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 Text (CSV) 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 Text (CSV) uses the last value. For example, if you specify 'ic', then HXTT Text (CSV) uses case-sensitive matching. If you specify a character other than those shown above, then HXTT Text (CSV) 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 Text (CSV) should begin the search. The default is 1, meaning that HXTT Text (CSV)begins 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 Text (CSV) replaces all occurrences of the match. If you specify a positive integer n, then HXTT Text (CSV) 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 Text (CSV) should return in relation to the occurrence: If you specify 0, then HXTT Text (CSV) returns the position of the first character of the occurrence. This is the default. If you specify 1, then HXTT Text (CSV) 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 Text (CSV) 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 Text (CSV) 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 Text (CSV) uses the last value. For example, if you specify 'ic', then HXTT Text (CSV) uses case-sensitive matching. If you specify a character other than those shown above, then HXTT Text (CSV) 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 Text (CSV) should begin the search. The default is 1, meaning that HXTT Text (CSV)begins 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 Text (CSV) replaces all occurrences of the match. If you specify a positive integer n, then HXTT Text (CSV) 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 Text (CSV) 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 Text (CSV) 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 Text (CSV) uses the last value. For example, if you specify 'ic', then HXTT Text (CSV) uses case-sensitive matching. If you specify a character other than those shown above, then HXTT Text (CSV) 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 Text (CSV) should begin the search. The default is 1, meaning that HXTT Text (CSV)begins 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 Text (CSV) 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 Text (CSV) 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 Text (CSV) uses the last value. For example, if you specify 'ic', then HXTT Text (CSV) uses case-sensitive matching. If you specify a character other than those shown above, then HXTT Text (CSV) 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 .
SWITCH ( expression1, value1, expression2, value2, ... expression_n, value_n )	any numeric, string, date/time, or boolean type,or null	Evaluates a list of expressions and returns the corresponding value for the first expression in the list that is TRUE.	SWITCH compares expression to the expression_n expressions and, if matches, returns value_n. If not, returns 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 Code Description @C CR is appended to positive currency or numeric values to indicate a credit. @D act as DTOS function. @E act as DTOS function. @T leading and trailing spaces are trimmed from character values. @X db is appended to negative currency or numeric values to indicate a debit. @Z if 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. @0 converts 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. X specifies the width of character values. For example, if cFormatCodes is XX? 2 characters are returned. Y converts 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. ARRAY_AGG([DISTINCT] expression): returns an array for input values, including null.
2. AVG([DISTINCT] expression): the average (arithmetic mean) of all input values.
3. AVERAGE(expression): the average (arithmetic mean) of all input values.
4. COUNT(*): the number of input values.
5. COUNT([DISTINCT] expression): the number of input values for which the value of expression is not null.
6. 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.
7. 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.
8. 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.
9. 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.
10. MAX(expression): the maximum value of expression across all input values.
11. MIN(expression): the minimum value of expression across all input values.
12. PRODUCT(expression): multiplies all the numbers of the input values.
13. STD(expression), STDDEV(expression), STDEVP(expression): the sample standard deviation of the input values.
14. STDEV(expression): the sample standard deviation of the input values. The denominator for dividing the sum of squared deviations is N-1, where N is the number of observations ( a count of items in the data set ). Technically, subtracting the 1 is referred to as "non-biased."
15. VARP(expression): the sample deviation of the input values.
16. VAR(expression): the sample deviation of the input values. The denominator for dividing the sum of squared deviations is N-1, where N is the number of observations ( a count of items in the data set ). Technically, subtracting the 1 is referred to as "non-biased."
17. STRING_AGG([DISTINCT] expression[, delimiter]): returns a string for input values, separated by delimiter.
18. SUM( [DISTINCT] expression): the sum of expression across all input values.
19. XMLAGG(expr [order_by_clause]): Returns a collection of XML fragments. Any arguments that return null are dropped from the result.

 

Table Functions

1. JSON_TABLE(jsonContent[,xPath]): JSON_TABLE is a powerful function that enables the easy decomposition of JavaScript Object Notation (JSON) data into relational format. It can be quoted as a table in SQL. You can use JSON_TABLE(?[,?]) in PreparedStatement to construct dynamic table. For instance, select * from json_table ('{"employees": [{ "firstName":"John" , "lastName":"Doe" },{ "firstName":"Anna" , "lastName":"Smith" },{ "firstName":"Peter" , "lastName":"Jones" }]}','/employees') as atable;
2. XML_TABLE(xmlContent[,xPath]): XML_TABLE is a powerful function that enables the easy decomposition of XML data into relational format. It can be quoted as a table in SQL. You can use XML_TABLE(?[,?]) in PreparedStatement to construct dynamic table. For instance, select * from xml_table ('<?xml version="1.0" encoding="ISO-8859-1"?><bookstore><book category="COOKING"> <title lang="en">Everyday Italian</title> <author>Giada De Laurentiis</author> <year>2005</year> <price>30.00</price></book><book category="WEB"> <title lang="en">Learning XML</title> <author>Erik T. Ray</author> <year>2003</year> <price>39.95</price></book></bookstore>','/bookstore');
3. VALUES_TABLE(valuesList[,columnNameList]): It can be quoted as a table in SQL. You can use VALUES_TABLE(?[,?]) in PreparedStatement to construct dynamic table. For instance, select * from values_table (((1,2),(3,4)),('column1','column2'));
   BTW, in SELECT sql, there is VALUES expression[, ...] ) AS tableName(columnName[,...])} .

 

ETL Functions

1. ISO3166(codeOrName[, wantType='Alpha-2']): ISO 3166 international standard provides all country ISO codes. These codes are used throughout the IT industry by computer systems and software to ease the identification of country names. wantType: 'alpha-2', 'alpha-3', 'code', 'name', 'name_CN', 'capital', 'continent'. For instance, select ISO3166('USA','Alpha-2'),ISO3166('United States','Alpha-3'),ISO3166('USA','Name'),ISO3166('USA','Code');
1. ETL_State(abbreviationOrName[, wantType='abbreviation'[,[countryCode=guess']]): It provides state (or province information) for ETL. Now it complements state or province information of two countries(Unite States, Canada). wantType: 'name','name_cn','abbreviation','capital','captial_cn','country'. For instance, select ETL_STATE('Alaska'),ETL_STATE('Alaska','Capital'),ETL_STATE('Ontario','abbreviation'),ETL_STATE('Ont.','abbreviation','CA'),ETL_STATE('Alaska;Ontario');
1. ETL_Zip(abbreviationOrName[, wantType='zip'[,[countryCode=guess']]): It provides zip (postal code) information for ETL. Now it complements zip (postal code) information of two countries(Unite States, Canada). wantType: 'state','zip' . For instance, select ETL_Zip('NY'),ETL_Zip('New York'),ETL_Zip('M6L','state'),ETL_State(ETL_Zip('M6L','state'),'name');