Numerical Function | Description |
ABS(d) | Returns the absolute value of a double value. |
ACOS(d) | Returns the arc cosine of an angle. |
ASIN(d) | Returns the arc sine of an angle. |
ATAN(d) | Returns the arc tangent of an angle. |
ATAN2(a,b) | Returns the tangent of a/b. |
BITAND(a,b) | Returns a and b. |
BITOR(a,b) | Returns a or b. |
CEILING(d) | Returns the smallest integer that is not less than d. |
COS(d) | Returns the cosine of an angle. |
COT(d) | Returns the cotangent of an angle. |
DEGREES(d) | Converts radians to degrees. |
EXP(d) | Returns e (2.718... raised to the power of d). |
FLOOR(d) | Returns the largest integer that is not greater than d. |
LOG(d) | Returns the natural logarithm (base e). |
LOG10(d) | Returns the logarithm (base 10). |
MOD(a,b) | Returns a modulo b. |
PI( ) | Returns pi (3.1415...). |
POWER(a,b) | Returns a raised to the power of b. |
RADIANS(d) | Converts degrees to radians. |
RAND( ) | Returns a random number x bigger or equal to 0.0 and smaller than 1.0. |
ROUND(a,b) | Rounds a to b digits after the decimal point. |
ROUNDMAGIC(d) | Solves rounding problems such as 3.11-3.1-0.01. |
SIGN(d) | Returns -1 if d is smaller than 0, 0 if d==0 and 1 if d is bigger than 0. |
SIN(d) | Returns the sine of an angle. |
SQRT(d) | Returns the square root. |
TAN(A) | Returns the trigonometric tangent of an angle. |
TRUNCATE(a,b) | Truncates a to b digits after the decimal point. |
String Function | Description |
ASCII(s) | Returns the ASCII code of the leftmost character of s. |
BIT_LENGTH(str) | Returns the length of the string in bits. |
CHAR(c) | Returns a character that has the ASCII code c. |
CHAR_LENGTH(str) | Returns the length of the string in characters. |
CONCAT(str1,str2) | Returns the string that results from concatenating str1 + str2. |
DIFFERENCE(s1,s2) | Returns an integer value from 0 to 4 that indicates the difference between the values returned by the SOUNDEX function for S1 and S2. A value of 4 indicates that S1 and S2 are the same, while a value of 0 indicates that the values have no similarity. |
HEXTORAW(s1) | Returns the binary format of the string. |
INSERT(s,start,len,s2) | Returns a string where len number of characters beginning at start has been replaced by s2. |
LCASE(s) | Converts s to lower case. |
LEFT(s,count) | Returns the leftmost count of characters of s. If s requires double quoting, use SUBSTRING( ) instead. |
LENGTH(s) | Returns the number of characters in s. |
LOCATE(search,s,[start]) | Returns the first index (1=left, 0=not found) where search is found in s, starting at start. |
LTRIM(s) | Removes all leading blanks in s. |
OCTET_LENGTH(str) | Returns the length of the string in bytes (twice the number of characters). |
RAWTOHEX(s1) | Returns translated string. |
REPEAT(s,count) | Returns s repeated count times. |
REPLACE(s,replace,s2) | Returns s with all occurrences of replace replaced with s2. |
RIGHT(s,count) | Returns the right-most count of characters of s. |
RTRIM(s) | Removes all trailing spaces in s. |
SOUNDEX(s) | Returns a 4-character code representing the sound of s. |
SPACE(count) | Returns a string consisting of count spaces. |
SUBSTR(s,start[,len]) | Alias for substring. |
SUBSTRING(s,start[,len]) | Returns the substring starting at start (1=left) with length len. |
UCASE(s) | Converts s to uppercase. |
LOWER(s) | Converts s to lowercase. |
UPPER(s) | Converts s to uppercase. |
Date/Time Function | Description |
CURDATE( ) | Returns the current date. |
CURTIME( ) | Returns the current time. |
DATEDIFF(string, datetime1, datetime2) | Returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values:  'ms'='millisecond''ss'='second''mi'='minute''hh'='hour''dd'='day''mm'='month''yy' = 'year'Both the long and short form of the strings can be used. |
DAYNAME(date) | Returns the name of the day. |
DAYOFMONTH(date) | Returns the day of the month (1-31). |
DAYOFWEEK(date) | Returns the day of the week (1 means Sunday). |
DAYOFYEAR(date | Returns the day of the year (1-366). |
HOUR(time) | Returns the hour (0-23). |
MINUTE(time) | Returns the minute (0-59). |
MONTH(date) | Returns the month (1-12). |
MONTHNAME(date) | Returns the name of the month. |
NOW( ) | Returns the current date and time as a timestamp, use CURRENT_TIMESTAMP instead. |
QUARTER(date) | Returns the quarter (1-4). |
SECOND(time) | Returns the second (0-59). |
WEEK(date) | Returns the week of this year (1-53). |
YEAR(date) | Returns the year. |
CURRENT_DATE | Returns the current date. |
CURRENT_TIME | Returns the current time. |
CURRENT_TIMESTAMP | Returns the current timestamp. |
System/Connection Function | Description |
DATABASE( ) | Returns the name of the database of this connection. |
USER( ) | Returns the user name of this connection. |
CURRENT_USER | SQL standard function, returns the user name of this connection. |
CURSESSIONID( ) | Returns the ID of the session (connection) on which this function was invoked. |
IDENTITY( ) | Returns the last identity value that was inserted by this connection. |
System Function | Description |
IFNULL(expr,value) | If expr is NULL, then value is returned; otherwise the result of expr is returned. See COALESCE for evaluating multiple expressions. |
CONVERT(term,type) | Converts term to another data type. |
CAST(term AS type) | Converts term to another data type. |
COALESCE(expr1, expr2, ...) | If expr1 is not Null, then it is returned; otherwise, expr2 is evaluated and, if not Null, it is returned, and so on. This is an ANSISQL standard system function. |
NULLIF(value1,value2) | If value1 equals value2, then Null is returned; otherwise, value1 is returned. |
CASE value1 WHEN value2 THEN value3 [ELSE value4] END | When value1 equals value2, then value3 is returned; otherwise, value4 or Null is returned in the absence of ELSE. |
CASE WHEN expr1 THEN value1 [WHEN expr2 THEN value2] [ELSE value4] END | When expr1 is true, then value1 is returned (optionally repeated for more cases); otherwise value4 or Null is returned in the absence of ELSE. |
EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE| SECOND} FROM datetime_value) | Any of the date and time terms can be extracted from datetime_value. |
POSITION(string_expression1 IN string_expression2) | If string_expression1 is a sub-string of string_expression2, then the position of the sub-string, counting from one, is returned; otherwise, 0 is returned. |
SUBSTRING(string_expression FROM numeric_expression1 [FOR numeric_expression2]) | string_expression is returned from the numeric_expression1 starting location. Optionally, numeric_expression2 specifies the length of the substring. |
TRIM([{LEADING | TRAILING | BOTH}] FROM string_expression) | When returned, either the leading or trailing spaces, or both, are trimmed from string_expression. |