| DB2 | Oracle |
1 | ABS(num), ABSVAL(num) | Get the absolute value | ABS(num) |
2 | ACOS(num) | Get the arc cosine | ACOS(num) |
3 | ADD_MONTHS(date, num) | Add num months to datetime | ADD_MONTHS(date, num) |
4 | ASCII(str) | Get ASCII code of left-most char | ASCII(str) |
5 | ASIN(num) | Get the arc sine | ASIN(num) |
6 | ATAN(num) | Get the arc tangent | ATAN(num) |
7 | ATAN2(x, y) | Get the arc tangent of x and y | ATAN2(y, x)  |
8 | ATANH(exp) | Get hyperbolic arctangent | |
9 | BIGINT(exp) | Convert to 64-bit integer | TRUNC(TO_NUMBER(exp)) |
10 | BITAND(exp1, exp2) | Perform bitwise AND | BITAND(exp1, exp2) |
11 | BITANDNOT(exp) | Perform bitwise AND NOT | |
12 | BITOR(exp) | Performs bitwise OR | |
13 | BITXOR(exp) | Performs bitwise exclusive OR | |
14 | BITNOT(exp) | Performs bitwise NOT | |
13 | BLOB(exp [,size]) | Convert to BLOB | TO_BLOB(exp) |
14 | CEILING(num) | Get the smallest following integer | CEIL(num) |
CEIL(num) |
15 | CHAR(string, num) | Truncate or pad string to num | RPAD(SUBSTR(string, 1, num), num) |
CHAR(date, USA) | Convert date to string | TO_CHAR(date, 'MM/DD/YYYY') |
16 | CHAR_LENGTH(string) | Get length of string in characters | LENGTH(string) |
CHARACTER_LENGTH(string) |
17 | CHAR_LENGTH(string, units) | Get length of string in units | LENGTH2(string), LENGTH4(string), LENGTHB(string) |
CHARACTER_LENGTH(string, units) |
18 | CLOB(exp [,size]) | Convert to CLOB | TO_CLOB(exp) |
19 | CHR(num) | Get character from ASCII code | CHR(num) |
20 | COALESCE(exp1, exp2, …) | Return first non-NULL expression | COALESCE(exp1, exp2, …) |
21 | CONCAT(str1, str2) | Concatenate strings | CONCAT(str1, str2) |
22 | COS(num) | Get the cosine | COS(num) |
23 | COT(num) | Get the cotangent | 1 / TAN(num) |
24 | CURRENT DATE | Get the current date | TRUNC(SYSDATE) |
CURRENT_DATE |
25 | CURRENT SCHEMA | Get the current schema | CURRENT_SCHEMA |
CURRENT_SCHEMA |
26 | CURRENT SERVER | Get the current database name | SYS_CONTEXT('USERENV', 'DB_NAME') |
CURRENT_SERVER |
27 | CURRENT SQLID | Get current schema | CURRENT_SCHEMA |
CURRENT_SQLID |
28 | CURRENT TIMESTAMP | Get the current date and time | CURRENT_TIMESTAMP |
CURRENT_TIMESTAMP |
29 | CURRENT TIME | Get the current time | SYSTIMESTAMP |
CURRENT_TIME |
30 | CURRENT USER | Get the authenticated user name | USER |
CURRENT_USER |
31 | CURSOR_ROWCOUNT(cur) | Get the number of fetched rows | cur%ROWCOUNT |
32 | DATE(timestamp) | Convert to DATE | TRUNC(timestamp) |
33 | DAY(datetime) | Extract day from datetime | EXTRACT(DAY FROM datetime) |
34 | DAYNAME(datetime) | Get the name of the weekday | TO_CHAR(datetime, 'Day') |
35 | DAYOFWEEK(datetime) | Get the weekday index | TO_NUMBER(TO_CHAR(datetime, 'D')) |
36 | DAYOFWEEK_ISO(exp) | Get the day of the week as int | TO_NUMBER(TO_CHAR(exp, 'D'))  |
37 | DAYOFYEAR(datetime) | Get the day of the year | TO_NUMBER(TO_CHAR(datetime, 'DDD')) |
38 | DAYS(exp) | Get the number of days | (exp - DATE '0001-01-02') |
39 | DBCLOB(exp [,size]) | Convert to DBCLOB | TO_CLOB(exp) |
40 | DECFLOAT(exp [,size]) | Convert to DECFLOAT | TO_NUMBER(exp) |
41 | DECFLOAT_FORMAT(exp [,fmt]) | Convert to DECFLOAT(34) | TO_NUMBER(exp [,fmt]) |
42 | DECIMAL | Convert to DECIMAL | TO_NUMBER |
43 | DEC |
44 | DECODE(exp, when, then, …) | Evaluate condition | DECODE(exp, when, then, …) |
45 | DEGREES(num) | Convert radians to degrees | (num) * 180/3.1415926535 |
46 | DEREF(exp) | Get instance of the target type | DEREF(exp) |
47 | DIGITS(exp) | Extract digits only | TRANSLATE(exp, '0-+.,', '0') |
48 | DOUBLE(exp) | Convert to DOUBLE | TO_NUMBER(exp) |
49 | DOUBLE_PRECISION(exp) |
50 | EMPTY_BLOB() | Get empty BLOB | EMPTY_BLOB() |
51 | EMPTY_CLOB() | Get empty CBLOB | EMPTY_CLOB() |
52 | EMPTY_DBCLOB() | Get empty DBCLOB | EMPTY_CLOB() |
53 | EMPTY_NCLOB() |
54 | EXP(n) | Raise e to the nth power | EXP(n) |
55 | EXTRACT(unit FROM datetime) | Extract unit from datetime | EXTRACT(unit FROM datetime) |
56 | FLOAT(n) | Convert to DOUBLE | TO_NUMBER(n) |
57 | FLOOR(num) | Get the largest preceding int | FLOOR(num) |
58 | GREATEST(exp, exp2, …) | Get the maximum value in a set | GREATEST(exp, exp2, …) |
59 | HEX(exp) | Convert to hex string | |
60 | HEXTORAW(exp) | Convert hex string to binary | HEXTORAW(exp) |
61 | HOUR(exp) | Extract hour from datetime | EXTRACT(HOUR FROM exp) |
62 | INITCAP(string) | Capitalize words | INITCAP(string) |
63 | INSERT(exp, start, len, ins) | Replace substring | User-defined function |
64 | INSTR(str, substr, pos, num) | Get position of substring | INSTR(str, substr, pos, num) |
65 | INSTRB(exp, search, start, num) | Get position of substring in bytes | INSTRB(exp, search, start, num) |
66 | INTEGER(exp) | Convert to integer | TRUNC(TO_NUMBER(exp)) |
67 | INT(exp) |
68 | JULIAN_DAY(exp) | Get Julian day | TO_NUMBER(TO_CHAR(exp, 'J')) |
69 | LAST_DAY(date) | Get last day of the month | LAST_DAY(date) |
70 | LCASE(string) | Lowercase string | LOWER(string) |
71 | LEAST(exp, exp2, …) | Get the minimum value in a set | LEAST(exp, exp2, …) |
72 | LEFT(string, n) | Get n leftmost characters | SUBSTR(string, 1, n) |
73 | LENGTH(string) | Get length of string in chars | LENGTH(string) |
74 | LN(exp) | Get natural logarithm | LN(exp) |
75 | LOCATE(substring, str, start) | Get position of substring | INSTR(str, substring, start)  |
76 | LOCATE_IN_STRING(exp, search, start, num) | Get position of substring | INSTR(exp, search, start, num) |
77 | LOG10(exp) | Get logarithm, base 10 | LOG(10, exp) |
78 | LONG_VARCHAR(exp) | Convert to LONG VARCHAR | TO_CLOB(exp) |
79 | LONG_VARGRAPHIC(exp) | Convert to LONG VARGRAPHIC | TO_CLOB(exp) |
80 | LOWER(string) | Lowercase string | LOWER(string) |
81 | LPAD(string, len) | Pad the left-side of string | LPAD(string, len) |
LPAD(string, len, pad) | LPAD(string, len, pad) |
82 | LTRIM(string) | Remove leading spaces | LTRIM(string) |
LTRIM(string, set) | Remove leading chars | LTRIM(string, set) |
83 | MAX(exp, exp2, …) | Get the maximum value in a set | GREATEST(exp, exp2, …) |
84 | MICROSECOND(exp) | Get the microsecond | TO_NUMBER(TO_CHAR(exp, 'FF6')) |
85 | MIDNIGHT_SECONDS(exp) | Get seconds since midnight | TO_NUMBER(TO_CHAR(exp, 'SSSSS')) |
86 | MIN(exp, exp2, …) | Get the minimum value in a set | LEAST(exp, exp2, …) |
87 | MINUTE(datetime) | Extract minute from datetime | EXTRACT(MINUTE FROM datetime) |
88 | MOD(dividend, divisor) | Get the remainder | MOD(dividend, divisor) |
89 | MONTH(date) | Extract month from date | EXTRACT(MONTH FROM date) |
90 | MONTHNAME(date) | Get the name of the month | TO_CHAR(date, 'Month') |
91 | MONTHS_BETWEEN(date1, date2) | Get number of months between date1 and date2 | MONTHS_BETWEEN(date1, date2) |
92 | MULTIPLY_ALT(exp, exp2) | Get product of the 2 arguments | (exp * exp2) |
93 | NCHAR(exp) | Convert to NCHAR | TO_NCHAR(exp) |
94 | NCLOB(exp [,size]) | Convert to NCLOB | TO_NCLOB(exp) |
95 | NVARCHAR | Convert to NVARCHAR | TO_NCHAR |
96 | NEXT_DAY(exp, weekday) | Get next weekday | NEXT_DAY(exp, exp2) |
97 | NULLIF(exp1, exp2) | Return NULL if exp1 = exp2 | NULLIF(exp1, exp2) |
98 | NVL(exp1, exp2) | Replace NULL with the specified value | NVL(exp1, exp2) |
NVL(exp1, exp2, …) | Return first non-NULL expression | COALESCE(exp1, exp2, …) |
99 | NVL2(exp1, exp2, exp3) | Return exp2 if exp1 is not NULL, otherwise exp3 | NVL2(exp1, exp2, exp3) |
100 | OCTET_LENGTH(exp) | Get length in bytes | LENGTHB(exp) |
101 | OVERLAY(exp, ins, start, len, unit) | Replace substring | User-defined function |
102 | POSITION(substring, exp, unit) | Get position of substring | INSTR(exp, substring)  |
103 | POSSTR(exp, substring) | Get position of substring | INSTR(exp, substring) |
104 | POWER(value, n) | Raise value to the nth power | POWER(value, n) |
105 | QUARTER(date) | Get the quarter of the year | TO_NUMBER(TO_CHAR(date, 'Q')) |
106 | RADIANS(numeric) | Convert degrees to radians | (numeric) * 3.1415926535/180 |
107 | RAISE_ERROR(sqlstate, exp) | Raise an error | RAISE_APPLICATION_ERROR  |
108 | RAND([integer]) | Get random float value in (0, 1) | DBMS_RANDOM.VALUE |
109 | REAL(exp) | Convert to REAL | TO_NUMBER(exp) |
110 | REPEAT(string, n) | Repeat string n times | RPAD(string, LENGTH(string) * n, string) |
111 | REPLACE(str, search) | Remove search-string | REPLACE(str, search) |
REPLACE(str, search, replace) | Replace search-string | REPLACE(str, search, replace) |
112 | RIGHT(string, n) | Get n rightmost characters | SUBSTR(string, -n)  |
113 | ROUND(num, integer) | Get rounded value | ROUND(num, integer) |
114 | ROUND_TIMESTAMP(exp [,format]) | Get rounded datetime | ROUND(exp [,format]) |
115 | RPAD(string, len) | Pad the right-side of string | RPAD(string, len) |
RPAD(string, len, pad) | RPAD(string, len, pad) |
116 | RTRIM(string) | Remove trailing spaces | RTRIM(string) |
RTRIM(string, set) | Remove trailing chars | RTRIM(string, set) |
117 | SECOND(datetime[, integer]) | Extract second from datetime | TRUNC(EXTRACT(SECOND FROM datetime)[, integer]) |
118 | SIGN(exp) | Get sign of exp | SIGN(exp) |
119 | SIN(num) | Get sine | SIN(num) |
120 | SINH(num) | Get hyperbolic sine | SINH(num) |
121 | SMALLINT(exp) | Convert to SMALLINT | TRUNC(TO_NUMBER(exp)) |
122 | SOUNDEX(string) | Get 4-character sound code | SOUNDEX(string) |
123 | SPACE(integer) | Get string of spaces | RPAD(' ', integer) |
124 | SQRT(num) | Get square root | SQRT(num) |
125 | STRIP(exp [,type, character]) | Remove characters | TRIM([type character FROM] exp) |
126 | SUBSTR(string, pos, len) | Get a substring of string | SUBSTR(string, pos, len) |
127 | SUBSTR2(exp, start [,len]) | Get a substring of exp | SUBSTR2(exp, start [,len]) |
128 | SUBSTRB(exp, start [,len]) | Get a substring of exp | SUBSTRB(exp, start [,len]) |
129 | SUBSTRING(exp, start [,len], unit) | Get a substring of exp | SUBSTR2(exp, start [,len]) |
SUBSTR4(exp, start [,len]) |
SUBSTRB(exp, start [,len]) |
130 | TAN(num) | Get tangent | TAN(num) |
131 | TANH(num) | Get hyperbolic tangent | TANH(num) |
132 | TIME(exp) | Get time | TO_TIMESTAMP(exp) |
133 | TIMESTAMP(exp) | Convert to TIMESTAMP | TO_TIMESTAMP(exp) |
134 | TIMESTAMP_FORMAT(exp) | Convert to TIMESTAMP | TO_TIMESTAMP(exp) |
135 | TIMESTAMP_ISO(exp) | Convert to TIMESTAMP | TO_TIMESTAMP(exp) |
136 | TIMESTAMPDIFF(exp, exp2) | Difference between two timestamps | User-defined function |
137 | TO_CHAR(exp [,format]) | Convert to string | TO_CHAR(exp [,format]) |
138 | TO_CLOB(exp [,size]) | Convert to CLOB | TO_CLOB(exp) |
139 | TO_DATE(exp) | Convert to TIMESTAMP | TO_DATE(exp) |
140 | TO_NCHAR(exp) | Convert to NCHAR | TO_NCHAR(exp) |
141 | TO_NCLOB(exp [,size]) | Convert to NCLOB | TO_NCLOB(exp) |
142 | TO_NUMBER(exp [,fmt]) | Convert to DECFLOAT(34) | TO_NUMBER(exp [,fmt]) |
143 | TO_SINGLE_BYTE(exp) | Convert to single-byte character | TO_SINGLE_BYTE(exp) |
144 | TO_TIMESTAMP(exp) | Convert to TIMESTAMP | TO_TIMESTAMP(exp) |
145 | TRANSLATE(exp, from, to) | Replace characters | TRANSLATE(exp, from, to) |
146 | TRIM([type trim FROM] string) | Remove characters | TRIM([type trim FROM] string) |
147 | TRUNC_TIMESTAMP(exp [,format] | Truncate TIMESTAMP | TRUNC(exp [,format] |
148 | TRUNC(exp, exp2) | Truncate exp | TRUNC(exp, exp2) |
149 | TRUNCATE(exp, exp2) | TRUNC(exp, exp2) |
150 | UCASE(string) | Uppercase string | UPPER(string) |
151 | UPPER(string) | Uppercase string | UPPER(string) |
152 | VALUE(exp, exp2, …) | Return first non-NULL expression | COALESCE(exp, exp2, …) |
153 | VARCHAR(exp [,exp2]) | Convert to VARCHAR | TO_CHAR(exp [,exp2]) |
154 | VARCHAR_BIT_FORMAT(exp [,fmt]) | Convert hex string to binary | HEXTORAW(exp) |
155 | VARCHAR_FORMAT(exp [,fmt]) | Convert to string | TO_CHAR(exp [,fmt]) |
156 | VARCHAR_FORMAT_BIT(exp [,fmt]) | Convert binary to hex string | RAWTOHEX(exp) |
157 | VARGRAPHIC(exp [,exp2]) | Convert to NCHAR | TO_NCHAR(exp [,exp2]) |
158 | WEEK(exp) | Get week of the year | TO_NUMBER(TO_CHAR(exp, 'WW'))  |
159 | WEEK_ISO(exp) | Get week of the year | TO_NUMBER(TO_CHAR(exp, 'IW')) |
160 | XMLATTRIBUTES(exp, …) | Construct XML attributes | XMLATTRIBUTES(exp, …) |
161 | XMLCOMMENT(exp) | Generate an XML comment | XMLCOMMENT(exp) |
162 | XMLCONCAT(exp, exp2, …) | Concatenate XML expressions | XMLCONCAT(exp, exp2, …) |
163 | XMLDOCUMENT(exp) | Get XML document | |
164 | XMLELEMENT(NAME exp) | Get an XQuery element node | XMLELEMENT(NAME exp) |
165 | XMLFOREST(exp, exp2, …) | Get a forest of XML expressions | XMLFOREST(exp, exp2, …) |
166 | XMLNAMESPACES(uri, …) | Get namespace | |
167 | XMLPARSE(DOCUMENT exp) | Parse XML document | XMLPARSE(DOCUMENT exp) |
168 | XMLPI(NAME identifier) | Get XML processing instruction | XMLPI(NAME identifier) |
169 | XMLQUERY(exp, …) | Convert XML data in SQL | XMLQUERY(exp, …) |
170 | XMLROW(exp, …) | Get XML document node | |
171 | XMLSERIALIZE(CONTENT exp AS datatype) | Get a serialized XML value | XMLSERIALIZE(CONTENT exp AS datatype) |
172 | XMLTEXT(exp, …) | Get XML text | |
173 | XMLVALIDATE(exp, …) | Get XML with information | |
174 | XMLXMLXSROBJECTID(exp) | Get XSR object | |
175 | XSLTRANSFORM(doc USING xslt) | Transform XML document | XMLTRANSFORM(doc, xslt) |
176 | YEAR(date) | Extract year from date | EXTRACT(YEAR FROM date) |