-- 값에서 검색값의 시작위치를 반환
oracle : select instr('달력1,달력2,달력3', ',', 1) from dual; ----> 4를 반환
mssql : select charindex(',', '달력1,달력2,달력3') ----> 4를 반환
-- 조건 비교 후 처리시
oracle : decode(조건, true값, false값)
mssql : case when 조건 then true값 else false값 end
-- 현재일에서 1달 뒤 구하기
oracle : SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;
mssql : SELECT DATEADD(mm, 1, GETDATE())
-- 날짜로 변환
oracle : SELECT TO_DATE('202005' || '01','YYYYMMDD' ) FROM DUAL;
mssql : SELECT CAST('202005' + '01' AS DATE)
-- 널값시 처리
oracle : NVL(END_YN, 'N')
mssql : ISNULL(END_YN, 'N')
-- 사용된 바이트 수를 반환합니다
oracle : vsize(expression)
mssql : DATALENGTH ( expression )
-- 조건문
oracle : IF 조건1 THEN 처리1 ELSIF 조건2 THEN 처리2 ELSE 처리3 END IF;
mssql : IF 조건1 처리1 ELSE IF 조건2 처리2 ELSE 처리3
-- 변수에 값 할당시
oracle : param := 0;
mssql : SET @param = 0;
-- 문자열 자르기
oracle : substr('20200520', 1, 4) --- 2020
mssql : substring('20200520', 1, 4)
oracle : substr('20200520', 0, 4) --- 2020
mssql : substring('20200520', 1, 4) -- 변환 작업시 주의해서 1로 수정해야 함
-- 절삭하기
oracle : SELECT TRUNC(150.75, 1) FROM DUAL;
mssql : SELECT ROUND(150.75, 1, 1);
-- 나머지 구하기
oracle : SELECT MOD(12,5) FROM DUAL;
mssql : SELECT 12%5
-- 두 기간의 월 수 구하기
oracle : SELECT TRUNC(MONTHS_BETWEEN(TO_DATE('2020-06-05','RRRR-MM-DD'), TO_DATE('2020-05-01','RRRR-MM-DD'))) "month" FROM DUAL;
mssql : SELECT DATEDIFF(MONTH, CAST('2020-05-01' AS DATE), CAST('2020-06-05' AS DATE))
-- 마지막 일자 구하기
oracle : select TO_CHAR(LAST_DAY(sysdate), 'DD') from dual
mssql : select DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(CONVERT(CHAR(6),GETDATE(),112)+'01' AS DATE))))
-- 올림
oracle : select ceil(1.1) from dual; -- 2
mssql : select ceiling(1,1) -- 2
-- ORACLE
SELECT LEVEL_CODE,
LAG(LEVEL_CODE) OVER (ORDER BY LEVEL_CODE) UPLEVEL,
LAG(GRADE_CODE) OVER (ORDER BY LEVEL_CODE) UPGRADE
FROM HR_CODE_LEVEL
-- MSSQL
SELECT A.LEVEL_CODE, B.LEVEL_CODE UPLEVEL, B.GRADE_CODE UPGRADE
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY LEVEL_CODE) ROWID, LEVEL_CODE, LEVEL_NAME, GRADE_CODE FROM HR_CODE_LEVEL) A
LEFT OUTER JOIN
(SELECT ROW_NUMBER() OVER(ORDER BY LEVEL_CODE) ROWID, LEVEL_CODE, LEVEL_NAME, GRADE_CODE FROM HR_CODE_LEVEL) B
ON
A.ROWID = B.ROWID+1
[출처] blog.naver.com/dbbible/221969929542
