|
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 함수
|
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() |
#################################################################
DECODE(if 문???)
#################################################################
ORACLE : DECODE( 칼럼, 값 , 참일때, 거짓일때)
SELECT DECODE(SEX,0,'남성', 1,'여성') AS SEX FROM 테이블명
MSSQL : CASE WHEN 조건문 then 참일때 ELSE 거짓일때
SELECT CASE WHEN SEX = 0 THEN '남성' ELSE '여성' END AS SEX FROM 테이블명
#################################################################
NVL
#################################################################
ORACLE : NVL( 칼럼, 값)
SELECT NVL(EMAIL,'3333') FROM WCM_USER
MSSQL : isnull(값 , 칼럼)
select isnull('33333333', email) as kakaka from wcm_user
#################################################################
프로시져 실행
#################################################################
ORACLE : BEGIN session_log_prc
MSSQL : exec rec_call 7
substr == substring
to_char == convert
--to_char(to_date(a.accept_da,'YYYY-MM-DD'), 'yyyy.mm.dd'),
convert(varchar(10),cast(a.accept_da as smalldatetime),102),
-- to_char(a.dr_amt, '999,999,999,999,999'),
subString(convert(varchar, cast(a.dr_amt as money ),1),1,len(convert(varchar, cast(a.dr_amt as money ),1))-3),
subString(convert(varchar, cast(a.cr_amt as money ),1),1,len(convert(varchar, cast(a.cr_amt as money ),1))-3),
--TO_CHAR(last_day(DATEADD(month,substring('20050127',1,6) + '01',-1)), 'YYYYMMDD')
dateadd(dd, -datepart(dd, cast('20050127' as smalldatetime)), dateadd(mm,1,cast('20050127' as smalldatetime)))
LPAD == replace
NVL == COALESCE
-- AND ROWNUM = 1
TOP 1
-- DECODE(dr_cr, 'D', SUM(currency_amt), 0) debt_amount,
-- DECODE(dr_cr, 'C', SUM(currency_amt), 0) credit_amount,
(case dr_cr when 'D' then SUM(currency_amt) else 0 end) debt_amount,
(case dr_cr when 'C' then SUM(currency_amt) else 0 end) credit_amount,
--select LPAD(' ',TO_NUMBER(s.accnt_gubun) * 4,' ') || s.accnt_detal_name from tb_erp_faccnt_cd s where s.accnt_cd = a.accnt_cd) accnt_detal_name ,
select accnt_cd, (select right(space(cast(s.accnt_gubun as int) * 4)+s.accnt_gubun,cast(s.accnt_gubun as int) * 4)+ s.accnt_detal_name from tb_erp_faccnt_cd s where s.accnt_cd = a.accnt_cd) accnt_detal_name ,
--to_char(add_months(to_date('@@accept_da_fr','yyyymmdd'),-12),'yyyymmdd')
convert(varchar(8),dateadd(month,-12,cast('@@accept_da_fr' as datetime)),112)
