[펌] 4대 RDBMS 함수 & 수식 요약

SQL Query Expressions and NULL function

	Oracle 8i~9i	DB2 UDB v7~8	MS SQL 2000	Sybase ASE 11~12.5
Comparison expressions	=, <, >, <=, >=, <>, !=, IS NULL, ANY, SOME, ALL	=, <, >, <=, >=, <>, !=, IS NULL, ANY, SOME, ALL	=, <, >, <=, >=, <>, !=, !>, !<, IS NULL, ANY, SOME, ALL	=, <, >, <=, >=, <>, !=, !>, !<, IS NULL, ANY, SOME, ALL
Logical expressions	AND, OR, IN, EXISTS, NOT, BETWEEN ~AND *Composite IN(subquery) *Composite IN(group)	AND, OR, IN, EXISTS, NOT, BETWEEN ~AND *Composite IN(subquery)	AND, OR, IN, EXISTS, NOT, BETWEEN ~AND	AND, OR, IN, EXISTS, NOT, BETWEEN ~AND
Bit operator	BITAND()	n/a	&, |, ^, ~	&, |, ^, ~
16bit expressions	TO_CHAR(n,'XXXX') HEXTORW()	HEX(), X’FF’	0xFF	0xFF, HEXTOINT(hex), INTTOHEX(num)
Pattern expressions	_, %	_, %	_, %, [a-z], [^a-z]	_, %, [a-z], [^a-z]
NULL functions	NVL(exp,ret) **NULLIF(exp1,exp2) DECODE(exp,…) **COALESCE(exp,…)	COALESCE(exp, ret) NULLIF(exp1,exp2)	ISNULL(exp,ret) NULLIF(exp1,exp2) COALESCE(exp,…)	ISNULL(exp,ret) NULLIF(exp1,exp2) COALESCE(exp,…)

*I.E.) Composite IN(subquery): ... Where (a,b,c) IN (SELECT c1, c2, c3 FROM tab...)...
Composite IN(group): ... Where (a,b,c) IN ( (1,2,3), (2,3,4), (3,4,5) …)

** COALESCE(), NULLIF() functions added in Oracle since version 9i

 

Environmental functions

	Oracle 8i~9i	DB2 UDB v7~8	MS SQL 2000	Sybase ASE 11~12.5
session ID	UID	-	SUSER_SID()	SUSER_ID()
session name	USER	USER	SUSER_SNAME()	SUSER_NAME()
seesion user	USER *CURRENT_USER	USER	USER_NAME() CURRENT_USER	USER_NAME()
date & time	SYSDATE CURRENT_DATE	CURRENT DATE, CURRENT TIME	GETDATE()	GETDATE()
time stamp	CURRENT_TIMESTAMP	CURRENT TIMESTAMP	CURRENT_TIMESTAMP	GETDATE()
host name	*HOST	CURRENT SERVER	HOST_NAME()	HOST_NAME()

* CURRENT_USER and HOST are only available through session context function in Oracle.
** CURRENT_TIMESTAMP and GETDATE() are exactly same

 

String functions

	Oracle 8i~9i	DB2 UDB v7~8	MS SQL 2000	Sybase ASE 11~12.5
Concatenate	a||b  or CONCAT(a,b)	a||b or CONCAT(a,b)	a+b	a+b
Length	LENGTH(str)    [char] LENGTHB(str)   [byte] LENGTHC(str)   [char]	LENGTH(str) [byte]	LEN(str) [char]	char_length(str) [byte]
A character Index (or location) in the string	INSTR(str,s[,p])   [chars] INSTRB(str,s[,p])  [byte] INSTRC(str,s[,p])  [chars]	LOCATE(s,str[,p]) [byte]	CHARINDEX(s,str[,p]) [chars]	CHARINDEX(s,str) [byte]
Sub string	SUBSTR(str,s[,l])  [chars] SUBSTRB(str,s[,l])  [byte] SUBSTRC(str,s[,l]) [chars]	SUBSTR(str,s[,l]) [byte]	substring(str,s,l) [chars]	substring(str,s,l) [byte]
Replacing	REPLACE(str,a[,b])	REPLACE(str,a,b)	REPLACE(str,a,b)	-
Case	UPPER(str) LOWER(str)	UPPER(str),UCASE(str) LOWER(str),LCASE(str)	UPPER(str), LOWER(str)	UPPER(str), LOWER(str)
Repeat	LPAD(str,l[,a]) RPAD(str,l[,a])	REPEAT(str,n)	REPLIATE(str,n)	REPLIATE(str,n)
Trim	LTRIM(str,s) RTRIM(str,s)	LTRIM(str) RTRIM(str)	LTRIM(str) RTRIM(str)	LTRIM(str) RTRIM(str)
ASCII	ASCII(str) CHR(code)	ASCII(str) CHR(code)	ASCII(str) CHAR(code)	ASCII(str) CHAR(code)

 

Conversion functions

	Oracle 8i~9i	DB2 UDB v7~8	MS SQL 2000	Sybase ASE 11~12.5
Multi type casting	CAST(exp AS type)	CAST(exp AS type)	CAST(exp AS type) CONVERT(type, exp[,op])	CONVERT(type, exp[,op])
Num -> Char	TO_CHAR(num[,fmt])	CHAR(num[,fmt]) DIGITS(num)	STR(num [,len [,d]])	STR(num [,len [,d]])
Char -> Num	TO_NUMBER(str[,fmt])	INTEGER(str) DOUBLE(str)	CONVERT()	CONVERT()
Date -> Char	TO_CHAR(date,fmt)	CHAR(date[,fmt])	CONVERT()	CONVERT()
Char -> Date	TO_DATE(str[,fmt])	TIMESTAMP(str) DATE(str)	CONVERT()	CONVERT()
conver to hexa	RAWTOHEX(bin) TO_CHAR(num,'XXXX')	HEX(num)	CONVERT()	HEXTOINT(hex) INTTOHEX(num)
translate	TRANSLATE(str,a,b)	TRANSLATE(str,b,a)	n/a	n/a

* ORACLE has CONVERT() function but it's converting character set enconding, not a type conversion.

 

Date Format Functions

Format	Oracle 8i/9i	MS SQL 2000/ Sybase ASE v12	DB2 UDB v7
default	yy/mm/dd  (Korean) dd-mon-yyyy  (US-English)	YYYY/MM/DD HH:MI:SS   (Korean) MM-DD-YYYY HH:MI:SS    (US-English) mon dd yyyy hh:miAM(PM) (Sybase only)	(timestamp)YYYY-MM-DD-HH:MI:SS.MMMMMM (date)YYYYMMDD (time)HH:MI:SS.MMMMMM
YYYY.MM.DD	TO_CHAR(x,‘YYYY.MM.DD’)	CONVERT(VARCHAR, x, 102)	REPLACE(CHAR(DATE(x),ISO), '-', '.')
HH:MM:SS	TO_CHAR(x,‘HH:MI:SS’)	CONVERT(VARCHAR, x, 108)	CHAR(TIME(x) , JIS )
YYYY/MM/DD	TO_CHAR(x,‘YYYY/MM/DD’)	CONVERT(VARCHAR, x, 111)	REPLACE(CHAR(DATE(x), ISO), '-', '/')
YYYYMMDD	TO_CHAR(x,‘YYYYMMDD’)	CONVERT(VARCHAR, x, 112)	CHAR(DATE(x))
HH24:MI:SS:MMM	TO_CHAR(x,‘HH24:MI:SS’)	CONVERT(VARCHAR(8), x, 114)	CHAR(TIME(x) )
YYYY.MM.DD HH24:MI	TO_CHAR(x,‘YYYY.MM.DD HH24:MI’)	CONVERT(VARCHAR, x, 102) + ' ' + CONVERT(VARCHAR(5), x, 114)	REPLACE(CHAR(DATE(x), ISO), '-', '.') || CAST( TIME(x) AS CHAR(5))
'YYYY/MM/DD HH24:MI:SS'	TO_CHAR(date_exp, 'YYYY/MM/DD HH24:MI:SS')	CONVERT(VARCHAR, date_exp, 111) + ' ' + CONVERT(VARCHAR(8), date_exp, 114)	REPLACE(CHAR(DATE(date_exp), ISO), '-', '/') || CAST( TIME(date_exp))

In Oracle, Session default can be replaced when the session start:

ALTER SESSION SET nls_date_format = 'YYYY.MM.DD';

ALTER SESSION SET nls_date_format = 'YYYY/MM/DD HH24:MI:SS';

 

Date Functions

	ORACLE 8~9i	DB2 UDB v7~8	MS SQL 2000	Sybase 11.x~12.5
Today time	SYSDATE CURRENT_DATE CURRRENT_TIMESTAMP	CURRENT DATE CURRENT TIME CURRENT TIMESTAMP	GETDATE() CURRENT_TIMESTAMP	GETDATE()
Date +/-	date +- day ADD_MONTHS(date, mo) MONTHS_BETWEEN(date, mo)	date +- n {DAYS|MONTHS|YEARS}	date +- day DATEADD(fmt,n,date) DATEDIFF(fmt,date,date)	DATEADD(fmt,n,date) DATEDIFF(fmt,date,date)
Day of week	TO_CHAR(date, fmt) NEXT_DAY(date, 요일)	DAYNAME(date) DAYOFWEEK(date) DAYOFWEEK_ISO(date)	DATENAME(fmt,date)	DATENAME(fmt,date)
Day	TO_CHAR(date, fmt) *EXTRACT(fmt FROM date)	DAY(date) MONTH(date) YEAR(date)	DATEPART(fmt,date) DAY(date) MONTH(date) YEAR(date)	DATEPART(fmt,date)
GMT 시간	SYS_EXTRACT_UTC(timestamp) TZ_OFFSET(DBTIMEZONE)	CURRENT TIMESTAMP CURRENT TIMEZONE	GETUTCDATE()	-
etc	ROUND(date[,fmt]) TRUNC(date[,fmt])	DAYOFYEAR(date) WEEK(date)

* Extract is not support lower than Oracle 9i

 

Math & Group function

Function	Oracle 8i~9i	DB2 UDB v8	MS SQL 2000	Sybase ASE 11~12.5
average	AVG(exp)	AVG(exp)	AVG(exp)	AVG(exp)
count	COUNT(exp|*)	COUNT(exp|*) COUNT_BIG(exp|*)	COUNT(exp|*) COUNT_BIG(exp|*)	COUNT(exp|*) COUNT_BIG(exp|*)
Max	MAX(exp)	MAX(exp)	MAX(exp)	MAX(exp)
Min	MIN(exp)	MIN(exp)	MIN(exp)	MIN(exp)
Standard Dev	STDDEV(exp)	STDDEV(exp)	STDEV(exp)
Sum	SUM(exp)	SUM(exp)	SUM(exp)	SUM(exp)
Variance	VARIANCE(exp)	VAR(exp)	VAR(exp)
Random number	*DBMS_RANDOM	RAND(seed)	RAND(seed)	RAND(seed)
Absolute value	ABS(n)	ABS(n)	ABS(n)	ABS(n)
Arc cosine	ACOS(n)	ACOS(n)	ACOS(n)	ACOS(n)
Arc sine	ASIN(n)	ASIN(n)	ASIN(n)	ASIN(n)
Arc tangent of n	ATAN(n)	ATAN(n)	ATAN(n)	ATAN(n)
Hyperbolic Arc tan	ATANH(n)	ATANH(n)
Min Fine number	CEIL(n)	CEIL(n)	CEILING(n)	CEILING(n)
Cosine	COS(n)	COS(n)	COS(n)	COS(n)
Hyperbolic cosine	COSH(n)	COSH(n)
CO TANGENT	COT(n)	COT(n)	COT(n)	COT(n)
Exponential value	EXP(n)	EXP(n)	EXP(n)	EXP(n)
Max Fine number	FLOOR(n)	FLOOR(n)	FLOOR(n)	FLOOR(n)
Greatest	GREATEST(n)
Least	LEAST(n)
Natural logarithm	LN(n)	LOG(n)	LOG(n)	LOG(n)
Logarithm, base 10	LOG(10,n)	LOG10(n)	LOG10(n)	LOG10(n)
Logarithm, any base	LOG(n)	LN(n)
Mod	MOD(m,n)	MOD(m,n)	% operator	% operator
power	POWER(m,n)	POWER(m,n)	POWER(m,n)	POWER(m,n)
round	ROUND(m,n)	ROUND(m,n)	ROUND(m,n)	ROUND(m,n)
+/- sign	SIGN(n)	SIGN(n)	SIGN(n)	SIGN(n)
Sine	SIN(n)	SIN(n)	SIN(n)	SIN(n)
Hyperbolic sine	SINH(n)	SINH(n)
sqrt	SQRT(n)	SQRT(n)	SQRT(n)	SQRT(n)
Tangent	TAN(n)	TAN(n)	TAN(n)	TAN(n)
Hyperbolic tangent	TANH(n)	TANH(n)
truncate	TRUNC(m,n)	TRUNC(m,n)	ROUND(m,n,<>0)	ROUND(m,n,<>0)

* DBMS_RANDOM is not a function, but a package in Oracle.

출처: http://knol.google.com/k/jeyong-park/sql-function-expression-comparison/3vywlm4f31xae/3#