- Posted by justin on September 28, 2004
Thanx to Dan McCulley for sending me this link. For all of you out there have to interact with both Oracle and SQL this is a handy reference.
http://www.webucator.com/resources/sql/reference.html
*same for both databases
|
|
| Absolute value | ABS | ABS |
| Arc cosine | ACOS | ACOS |
| Arc sine | ASIN | ASIN |
| Arc tangent of n | ATAN | ATAN |
| Arc tangent of n and m | ATAN2 | ATN2 |
| Smallest integer >= value | CEIL | CEILING |
| Cosine | COS | COS |
| Hyperbolic cosine | COSH | COT |
| Exponential value | EXP | EXP |
| Round down to nearest integer | FLOOR | FLOOR |
| Natural logarithm | LN | LOG |
| Logarithm, any base | LOG(N) | N/A |
| Logarithm, base 10 | LOG(10) | LOG10 |
| Modulus (remainder) | MOD | USE MODULO (%) OPERATOR |
| Power | POWER | POWER |
| Random number | N/A | RAND |
| Round | ROUND | ROUND |
| Sign of number | SIGN | SIGN |
| Sine | SIN | SIN |
| Hyperbolic sine | SINH | N/A |
| Square root | SQRT | SQRT |
| Tangent | TAN | TAN |
| Hyperbolic tangent | TANH | N/A |
| Truncate | TRUNC | N/A |
| Highest number in list | GREATEST | N/A |
| Lowest number in list | LEAST | N/A |
| Convert number if NULL | NVL | ISNULL |
|
|
| Convert character to ASCII | ASCII | ASCII |
| String concatenate | CONCAT | (expression + expression) |
| Convert ASCII to character | CHR | CHAR |
| Return starting point of character in character string (from left) | INSTR | CHARINDEX |
| Convert characters to lowercase | LOWER | LOWER |
| Convert characters to uppercase | UPPER | UPPER |
| Pad left side of character string | LPAD | N/A |
| Remove leading blank spaces | LTRIM | LTRIM |
| Remove trailing blank spaces | RTRIM | RTRIM |
| Starting point of pattern in character string | INSTR | PATINDEX |
| Repeat character string multiple times | RPAD | REPLICATE |
| Phonetic representation of character string | SOUNDEX | SOUNDEX |
| String of repeated spaces | RPAD | SPACE |
| Character data converted from numeric data | TO_CHAR | STR |
| Substring | SUBSTR | SUBSTRING |
| Replace characters | REPLACE | STUFF |
| Capitalize first letter of each word in string | INITCAP | N/A |
| Translate character string | TRANSLATE | N/A |
| Length of character string | LENGTH | DATALENGTH or LEN |
| Greatest character string in list | GREATEST | N/A |
| Least character string in list | LEAST | N/A |
| Convert string if NULL | NVL | ISNULL |
|
|
| Date addition | (use +) | DATEADD |
| Date subtraction | (use -) | DATEDIFF |
| Last day of month | LAST_DAY | N/A |
| Time zone conversion | NEW_TIME | N/A |
| First weekday after date | NEXT_DAY | N/A |
| Convert date to string | TO_CHAR | DATENAME |
| Convert date to number | TO_NUMBER(TO_CHAR()) | DATEPART |
| Convert string to date | TO_DATE | CAST |
| Get current date and time | SYSDATE | GETDATE() |