|
|
There's no way any of us can memorize all the functions in one system, let alone several. The following tables compare functions by task for each of the systems we use in our examples in this book.
SQLServer | Access | Oracle | MySQL |
---|---|---|---|
AVG(domain) | DAVG(field,domain, criteria) | AVG(domain) | AVG(domain) |
COUNT(domain) | COUNT(domain) | COUNT(domain) | DCOUNT(field,domain, criteria) |
MAX(domain) | DMAX(field,domain, criteria | MAX(domain) | MAX(domain) |
MIN(domain) | DMIN(field,domain, criteria | MIN(domain) | MIN(domain) |
SUM(domain) | DSUM(field,domain, criteria) | SUM(domain) | SUM(domain) |
STDEV(domain) | DSTDEV(field,domain, criteria) | STDEV(domain) | STDEV(domain) |
STDEVP(domain) | DSTDEVP(field, domain,criteria) | STDEVP(domain) | STDEVP(domain) |
VAR(domain) | DVAR(field,domain, criteria) | VARIANCE(domain) | |
VARP(domain) | DVARP(field,domain, criteria) | VARP(domain) | |
DLOOKUP(field, domain,criteria) | DECODE(expr1,expr2) |
SQL Server | Access | Oracle MySQL | |
---|---|---|---|
ABS(value) | ABS(value) | ABS(value) | ABS(value) |
GREATEST(value1, value2) | GREATEST(value1, value2) | ||
LEAST(value1,value2) | LEAST(value1,value2) | ||
ROUND(value,length, function) | ROUND(number,decimal places) | ROUND(value, precision) | |
TRUNC(number,decimal places) | TRUNCATE(value, precision) | ||
CEILING(value) | CEILING(value) | ||
FLOOR(value) | |||
FLOOR(expr) | |||
RAND(value) | RND(value) | RAND(seed) |
SQL Server | Access | Oracle | MySQL |
---|---|---|---|
FORMAT(string,code) | |||
LEN(string) | LEN(string) | LENGTH(string) | LENGTH(string) |
LOWER(string) | LCASE(string) | LOWER(string) | LCASE(string) / LOWER(string) |
SUBSTRING (searchstring,begin, length) | INSTR(start,string1, string2,compare) | SUBSTR(string, length) | INSTR(string, searchstring) |
UPPER(string) | UCASE(string) | UPPER(string) | UCASE(string) / UPPER(string) |
SOUNDEX(string) | SOUNDEX(string) | ||
DIFFERENCE (string,...) | |||
STUFF(string,start, length,substring) | |||
ASCII(character) | ASC(character) | ASCII(character) | |
CHAR(value) | CHR(value) | ||
LEFT(string,number) | LEFT(string,number) | LEFT(string,number) | |
LTRIM(string) | LTRIM(string) | LTRIM(string) / TRIM(string) | |
SPLIT operator | SPLIT(string, delimiter) | ||
RIGHT(string, number) | RIGHT(string, number) | RIGHT(string,number) | |
RTRIM(string) | RTRIM(string) | RTRIM(string) / TRIM(string) | |
SPACE(value) | SPACE(value) | SPACE(value) | |
STR(value) | STR(value) | ||
STRCOMP(string1, string2) | |||
STRREVERSE(string)VAL(string) | |||
CONCATENATION operator | CONCAT(string1, string2,...) | ||
MID(string,begin, number) |
SQL Server | Access | Oracle | MySQL |
---|---|---|---|
DATEADD(code, interval,date) | DATEADD(code, interval,date) | ADD_MONTHS(date, numberofmonths) | DATE_ADD(date, interval,type)/ ADDDATE(date, interval,type) |
LAST_DAY(date) | |||
DATEDIFF(code,date, datestring) | DATEDIFF(code, interval,date) | MONTHS_BETWEEN (date1,date2) | PERIOD_DIFF (date1,date2) |
NEXT_DAY(date, dayname) | |||
ROUND(date/ time,format) | |||
GETDATE() | NOW(),DATE() | SYSDATE() | NOW(),SYSDATE() |
TRUNC(date/time) | |||
DATEPART(code,date) | DATEPART(code,date) | ||
DAY(date) | DAY(date) | DAYOFWEEK(date), WEEKDAY(date), DAYNAME(date) | |
MONTH(date) | MONTH(date) | MONTH(date) | |
YEAR(date) | YEAR(date) | YEAR(date) | |
DATESERIAL(year, month,day) | |||
DATEVALUE(datestring) | |||
DATEPART('hh',time) | HOUR(time) | HOUR(time) | |
DATEPART('mi',time) | MINUTE(time) | MINUTE(time) | |
DATEPART('ss',time) | SECOND(time) | SECOND(time) | |
DATEPART('dw',date) | WEEKDAY(date) | DAYOFWEEK(date), | |
WEEKDAY(date) | |||
TIMESERIAL(hour, minute,seconds) | |||
DAYOFYEAR(date) | |||
DAYNAME(date) | |||
MONTHNAME(date) |
SQL Server | Access | Oracle | MySQL |
---|---|---|---|
CAST(expression as date) CONVERT(datatype, expression) | CDATE(value) | TO_DATE(expression, format) | CAST(expression as datatype) |
CAST(expression AS datatype), CONVERT(datatype, expression) | TO_CHAR (date, format) | ||
CAST(expression as datatype), CONVERT(datatype, expression) | TO_NUMBER (string, format) | ||
CAST(expression as datatype), CONVERT(datatype, expression) | CBOOL(value) | ||
CAST(expression as datatype), CONVERT(datatype, expression) | CBYTE(value) | ||
CAST(expression as datatype), CONVERT(datatype, expression) | CDBL(value | ||
CAST(expression as datatype), CONVERT(datatype, expression) | NZ(variant, valueifnull) | NVL(expression1, expression2) | |
CAST(expression as datatype), CONVERT(datatype, expression) | CINT(value) | CAST(expression as signed|unsigned) | |
CAST(expression as datatype), CONVERT(datatype, expression) | CLNG(value) | ||
CAST(expression as datatype), CONVERT(datatype, expression) | CSNG(value) | ||
CAST(expression as datatype), CONVERT(datatype, expression) | CSTR(value) |
|
|