여러가지 DBMS를 쓰다보면 함수 사용시 큰 혼란을 겪게된다.
가장 많이 사용되는 DBMS인 Oracle과 MSSQL의 대표적인 산술, 문자열, 날짜 함수를 정리하면 아래와 같다.
=======================================================================================================
|
Math Functions | ||
| Function | Oracle | SQL Server |
| 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 |
| String Functions | ||
| Function | Oracle | SQL Server |
| 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 Functions | ||
| Function | Oracle | SQL Server |
| 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() |
출처 : http://arraybox.com
