Database2009. 4. 3. 14:00

여러가지 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

Posted by Huikyun