|
Numerical Built-In Functions
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)
|
Return a & b
|
BITOR(a,b)
|
Returns a | 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 Built-In Functions
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 str1 + str2
|
DIFFERENCE(s1,s2)
|
Returns the difference between the sound of s1 and s2
|
HEXTORAW(s1)
|
Returns translated 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
|
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
|
LOWER(s)
|
Converts s to lower case
|
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)
|
Replaces all occurrences of replace in s with s2
|
RTRIM(s)
|
Removes all trailing spaces
|
SOUNDEX(s)
|
Returns a four 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 upper case
|
UPPER(s)
|
Converts s to upper case
|
Date/Time Built-In Functions
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)
|
Return 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
|
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
|
System Built-In Functions
Function
|
Description
|
CONVERT(term,type)
|
Converts exp to another data type. Supported data types:
| • | Integer types: TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC, DECIMAL, BOOLEAN |
| • | Floating point types: REAL, FLOAT, DOUBLE |
| • | String types: CHAR, VARCHAR, LONGVARCHAR |
| • | Date/time types: DATE, TIME, TIMESTAMP |
|
CASEWHEN(exp,v1,v2)
|
If exp is true, v1 is returned, else v2
|
COALESCE(expr1,expr2,expr3,...)
|
If expr1 is not null then it is returned else, expr2 is evaluated and if not null it is returned and so on
|
NULLIF(v1,v2)
|
If v1 equals v2 return null, otherwise v1
|
|